Write c# code that auto add stored procedure at run time

Abraham Olatubosun

Dear Experts,

A blessed day to you all.

I am writing a program that generate Patient Level Data to be deployed in 200 branches already have existing DataBase. Kindly show me way that my C# code can add my stored procedure at run time to the existing database.

thank you all

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abraham Olatubosun9y ago
Thank you all for all the effort i was able to resolve it, below is my solution other can view this and learn
stringSQL26=string.Concat("IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME='tbl_LabTest')",
"BEGIN",
"TRUNCATETABLEtbl_LabTest",
"END",
"SETIDENTITY_INSERTtbl_LabTestON",
"INSERTINTOtbl_LabTest([CODE],[CODE_DESC])",
"VALUES('1','Albumin(g/L)'),",
"('2','ALT/SGPT'),",
"('3','Amylase'),",
"('4','AST/SGOT'),",
"('5','BilirubinDirect(µmol/L)'),",
"('6','BilirubinIndirect(µmol/L)'),",
"('7','BilirubinTotal(µmol/L)'),",
"('8','BloodUreaNitrogen(µmol/L)'),",
"('9','Calcium(mmol/L)'),",
"('10','CalciumIonized(mmol/L)'),",
"('82','WBCDiff')",
"SETIDENTITY_INSERT[dbo].[tbl_LabTest]OFF");
Abraham Olatubosun9y ago
Dear Experts,
I thank you all for the kind hearted tips all this day's.
ButNitin Sontakke, i have corrected all the errors i saw but i still get these errors:
Incorrectsyntaxnear'1'.
Incorrectsyntaxnear'2'.
Incorrectsyntaxnear'3'.
Incorrectsyntaxnear'4'.
Incorrectsyntaxnear'5'.
Incorrectsyntaxnear'6'.
Incorrectsyntaxnear'7'.
Incorrectsyntaxnear'8'.
Incorrectsyntaxnear'9'.
the code that giving these error is
stringSQL26="IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME='tbl_LabTest')";
SQL26+="BEGIN";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''1'',''Albumin(g/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''2'',''ALT/SGPT'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''3'',''Amylase'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''4'',''AST/SGOT'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''5'',''BilirubinDirect(µmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''6'',''BilirubinIndirect(µmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''7'',''BilirubinTotal(µmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''8'',''BloodUreaNitrogen(µmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''9'',''Calcium(mmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''10'',''CalciumIonized(mmol/L)'')";
SQL26+="INSERTINTOtbl_LabTest([CODE],[CODE_DESC])VALUES(''82'',''WBCDiff'')";
SQL26+="END";
SqlCommandcmd26=newSqlCommand(SQL26,con);
cmd26.ExecuteNonQuery();
cmd26.Dispose();
Nitin Sontakke9y ago
Wherever you are ending the string, just put the space before double quotation mark.

Also, do one thing, just see the output of the SQL26 variable and you will notice what SQL Server is getting to execute.
Abraham Olatubosun9y ago
thank you guys i really appreciate you timely response.
however below is the code i want to implement, it should create stored procedure at run time and add specific data to it but my code is generating exception error at the point of INSERTING data's, any help will be appreciated.
Create Stored Procedure this work fine
stringSQL26A="IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME='tbl_LabTest')";
SQL26A+="BEGIN";
SQL26A+="DROPTABLEtbl_LabTest";
SQL26A+="END";
SQL26A+="ELSE";
SQL26A+="BEGIN";
SQL26A+="CREATETABLE[dbo].[tbl_LabTest](";
SQL26A+="[num][int]IDENTITY(1,1)NOTNULL,";
SQL26A+="[CODE][nchar](10)NULL,";
SQL26A+="[CODE_DESC][nvarchar](100)NULL";
SQL26A+=")ON[PRIMARY]";
SQL26A+="END";
SqlCommandcmd26A=newSqlCommand(SQL26A,con);
cmd26A.ExecuteNonQuery();
cmd26A.Dispose();
This part is where i get the exceptional error
string SQL26="IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='tbl_LabTest')";
SQL26 +="BEGIN";
SQL26 +="SET IDENTITY_INSERT tbl_LabTestON";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''1'',''Albumin(g/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''2'',''ALT/SGPT'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''3'',''Amylase'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''4'',''AST/SGOT'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''5'',''BilirubinDirect(µmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''6'',''BilirubinIndirect(µmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''7'',''BilirubinTotal(µmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''8'',''BloodUreaNitrogen(µmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''9'',''Calcium(mmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''10'',''CalciumIonized(mmol/L)'')";
SQL26 +="INSERT INTO tbl_LabTest([CODE],[CODE_DESC])VALUES(''82'',''WBCDiff'')";
SQL26 +="SET IDENTITY_INSERT[dbo].[tbl_LabTest]OFF";
SQL26 +="END";
SqlCommandcmd26=newSqlCommand(SQL26,con);
cmd26.ExecuteNonQuery();
cmd26.Dispose();
Manas Mohapatra9y ago
I think Ananth provided the answer.
If you have different requirement please describe more..
Nitin Sontakke9y ago
I think your requirement is not quite clear to me. It would be nice to know what exactly you are trying to achieve in the end.

Many a times, there are several ways to resolve the issue than we think of.
Ananth G9y ago
I am explaining how to call stored procedures that return data using ADO.Net. you will make some changes for your requirement below
Connection String
add this code to web config
<connectionStrings>
<add name="conString"
connectionString="DataSource=Your Data source;
database=Your Database Name;IntegratedSecurity=true"/>
connectionStrings>
Namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Select Stored Procedures
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllEmployeeDetails]
AS
BEGIN
      SET NOCOUNT ON;
      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees
END
C# coding
String strConnString =  ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con =  new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAllEmployeeDetails";
cmd.Connection = con;
try
{
    con.Open();
    GridView1.EmptyDataText = "No Records Found";
    GridView1.DataSource = cmd.ExecuteReader() ;
    GridView1.DataBind();
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    con.Close();
    con.Dispose();
}