sql;
cmd.ExecuteNonQuery();
}
}
///
/// 获取存储过程的参数列表
///
/// 存储过程名称
/// DataTable
private static DataTable GetParameters(SqlConnection conn, string proc_Name)
{
SqlCommand comm = new SqlCommand("dbo.sp_sproc_columns", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@procedure_name", (object)proc_Name);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
///
/// 为 SqlCommand 添加参数及赋值
///
/// SqlCommand
/// 参数数组(必须遵循存储过程参数列表的顺序)
private static void AddInParaValues(SqlCommand comm, params object[] paraValues)
{
using (SqlConnection conn = DBConn.GetConn())
{
comm.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int));
comm.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
if (paraValues != null)
{
DataTable dt = GetParameters(conn, comm.CommandText);
int i = 0;
foreach (DataRow row in dt.Rows)
{
string key = row[3].ToString();
if (key != "@RETURN_VALUE")
{
int value = int.Parse(row[4].ToString());
if (value == 1)
{
comm.Parameters.AddWithValue(key, paraValues[i]);
}
else if (value == 2)//value为2则是输出参数
{
comm.Parameters.AddWithValue(key, paraValues[i]).Direction = ParameterDirection.Output;
//comm.Parameters[key].Direction = ParameterDirection.Output;
}
comm.Parameters[key].Size = Convert.ToInt32(row[7].ToString());
i++;
}
}
}
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace DBComm
{
class DBConn
{
private static string m_connstr;
public static string ConnString
{
get { return m_connstr; }
private set { m_connstr = value; }
}
static DBConn()
{
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.DataSource = ".";
connStr.InitialCatalog = "test";
connStr.IntegratedSecurity = true;
connStr.Pooling = true; //开启连接池
connStr.MinPoolSize = 0; //设置最小连接数为0
connStr.MaxPoolSize = 100; //设置最大连接数为100
connStr.ConnectTimeout = 10; //设置超时时间为10秒
ConnString = connStr.ConnectionString;
//ConnectDB(ConnString);
}
public static SqlConnection GetConn()
{
SqlConnection conn = new SqlConnection(ConnString);
conn.Open();
return conn;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace DBComm
{
static class DBTableSource
{
public static DataTable GetSource(SqlConnection conn, string strsql)
{
DataTable dt = null;
SqlCommand cmd = null;
SqlDataAdapter ad = null;
try
{
lock (dt = new DataTable())
{
if (conn is SqlConnection)
{
cmd = new SqlCommand(strsql, conn);
ad = new SqlDataAdapter((SqlCommand)cmd);
}
dt.Clear();
ad.Fill(dt);
}
}
catch (Exception e)
{
throw e;
}
return dt;
}
public static DataTable Source(string strsql)
{
using (SqlConnection conn = DBConn.GetConn())
{
return GetSource(conn, strsql);
}
}
}
}