调用带输入输出参数的存储过程
View Code
public string GetDataSetG(string patientid) { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString() SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "usp_GetCount"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter sp = new SqlParameter("PatientId", patientid); sp.DbType = DbType.Int32; sp.Direction = ParameterDirection.Input; cmd.Parameters.Add(sp); sp = new SqlParameter("count", SqlDbType.NVarChar,100); //这里是string类型 //sp.ParameterName = "count"; //这里是int类型 //sp.DbType = DbType.int; sp.Direction = ParameterDirection.Output; cmd.Parameters.Add(sp); cmd.ExecuteNonQuery(); string s = sp.Value.ToString(); conn.Close(); return s; }
调用带输入参数的存储过程
View Code
Private int GetSqlComand(int customerId){ string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString() SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand cmd = new SqlCommand("Proc_DeleteCustomerById",conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter para = new SqlParameter("@CustomerId",customerId);cmd.Parameters.Add(para); //执行命令,并返回受影响的行数return cmd.ExecuteNonQuery(); }
调用带多个输入参数的存储过程
View Code
Private int GetSqlComand(int customerId){ string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString() SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand cmd = new SqlCommand("Proc_DeleteCustomerById",conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@CustomerId",customerId), new SqlParameter("@CustomerName",customerName) }; foreach (SqlParameter pa in parms) cmd.Parameters.Add(pa);//执行命令,并返回受影响的行数return cmd.ExecuteNonQuery(); }
调用带sql结果集的存储过程
存储过程代码 CREATE PROCEDURE [dbo].[sp_PatientVisit] (@pid int,--病人id @vid int,--随访id @biaoji int ) AS BEGIN declare @sql varchar(2000) if(@biaoji=0) begin if(@vid=0) begin SET @sql='select * from hsopital' end else begin set @sql='select * from Section' end end else if(@biaoji=1) begin set @sql='select * from doctor' end exec (@sql) END
调用方法
private DataSet GetDataSet(string sql) { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnString"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); conn.Close(); cmd.Dispose(); return ds; } 传进去的结果 DataSet dsgg = GetDataSet(string.Format("exec usp_GetCount {0}", id));