How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?
Sudhir Goswami
By sqlCommand
sqlCommand cmd=new SqlCommand("Sp_Insert"); cmd.commandtype=CommandType.StoredProcedurecmd.parameter.AddWithValues("@name"); cmd.Parameter.AddWithValues("@salary");
create proc Sp_Insert(@Name,@Salary) as begin Insert into UserInfo(Name, salary) values(@name,@salary) end//
hello friend,now i m explaining u to use of SP ADO.NET. this is Example of Code of Business Layer which is using SP from Data Base to .net. hope it would be help full for you. i m explaining you for insert SP.// subject details insertpublic int subject_details_i(int subject_id, int class_id, string class_description, Int32 theory, Int32 practical, Int32 compulsory, string marking_type, string year){int li_validated = 0;string msg = "";MySqlParameter[] myParams = new MySqlParameter[9];myParams[0] = new MySqlParameter("?p_subject_id", MySqlDbType.Int32);myParams[0].Value = subject_id;myParams[1] = new MySqlParameter("?p_class", MySqlDbType.VarChar, 45);myParams[1].Value = class_description;myParams[2] = new MySqlParameter("?p_theory", MySqlDbType.Int32);myParams[2].Value = theory;myParams[3] = new MySqlParameter("?p_practical", MySqlDbType.Int32);myParams[3].Value = practical;myParams[4] = new MySqlParameter("?p_compulsory", MySqlDbType.Int32);myParams[4].Value = compulsory;myParams[5] = new MySqlParameter("?p_marking_type", MySqlDbType.VarChar, 45);myParams[5].Value = marking_type;myParams[6] = new MySqlParameter("?p_user", MySqlDbType.VarChar, 45);myParams[6].Value = Resources.User;myParams[7] = new MySqlParameter("?p_year", MySqlDbType.VarChar, 45);myParams[7].Value = year;myParams[8] = new MySqlParameter("?p_class_id", MySqlDbType.Int32);myParams[8].Value = class_id;try{gDB.OpenConnection();li_validated = gDB.ExecuteNonQuery("ps_subject_details_i", myParams);gDB.CloseConnection();}catch (Exception ex){msg = ex.Message;}return li_validated;}