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

Question

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

Ananth G · Answer

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 =Data Source =Your Data source; database = Your Database Name ;Integrated Security = 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(); }

Abraham Olatubosun · Answer

Thank you all for all the effort i was able to resolve it below is my solution other can view this and learn string SQL26= 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 Olatubosun · Answer

Dear Experts I thank you all for the kind hearted tips all this day's. But Nitin 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 string SQL26= 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= new SqlCommand(SQL26con); cmd26.ExecuteNonQuery(); cmd26.Dispose();

Nitin Sontakke · Answer

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 Olatubosun · Answer

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 string SQL26A= IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME='tbl_LabTest') ; SQL26A+= BEGIN ; SQL26A+= DROPTABLEtbl_LabTest ; SQL26A+= END ; SQL26A+= ELSE ; SQL26A+= BEGIN ; SQL26A+= CREATETABLE[dbo].[tbl_LabTest]( ; SQL26A+= [num][int]IDENTITY(11)NOTNULL ; SQL26A+= [CODE][nchar](10)NULL ; SQL26A+= [CODE_DESC][nvarchar](100)NULL ; SQL26A+= )ON[PRIMARY] ; SQL26A+= END ; SqlCommandcmd26A= new SqlCommand(SQL26Acon); 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= new SqlCommand(SQL26con); cmd26.ExecuteNonQuery(); cmd26.Dispose();

Manas Mohapatra · Answer

I think Ananth provided the answer. If you have different requirement please describe more..

Nitin Sontakke · Answer

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.