In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,
- First you need to setup SqlDataAdapter
- Create Parameter Objects
- Create SQL Procedure with Parameters
Lets do one by one
SqlDataAdapter
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["MACCon"].ConnectionString;
con.Open();
ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.
Setting up Parameters with values
ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();
with the SqlParameter we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.
Design your SQL Stored Procedure
Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.
ALTER PROCEDURE dbo.CRUDS_PRODUCTMASTER ( @item nchar(10), @mfr nchar(10), ) AS Begin insert into productmaster (item,mfr) values(@item,@mfr,); End
The parameters in C# and SQL should match otherwise it will cause errors.
