string strSql = "select UserName,UserPass from tbUserInfo where UserName=@username and UserPass=@userpass";
SqlParameter[] sqlpUser ={ new SqlParameter("@username",SqlDbType.NVarChar,30);new SqlParameter("@userpass",SqlDbType.NVarChar,30); } sqlpUser.Value = tbName.Text;
sqlpPass.Value = tbPass.Text;
com.Parameters.Add(sqlpUser);
com.Parameters.Add(sqlpPass);一个页面有20多个字段，如果又要new 参数，又要赋值 又要add。添加数据就要写很多代码。或者把字符类型"SqlDbType.NVarChar,30" 放在一个列表里，用for循环new 参数，然后用for循环赋值和add有没有更精简的写法？
或
SqlParameter []parameter=new SqlParameter[]{new SqlParameter("@strID",strID)};
或
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Connection );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
如果一点都不一样，那么怎么FOR呢。有一种做法是只有一个参数。就是XML,
不管是30个还是100个，都放在XML中，传给数据库，
由数据库解析。
{ String getConnectionString = "Application Name=sss;Initial Catalog=DEVDB;Data Source=10.3.1.218;User ID=sa;password=sa;Pooling=True";
SqlConnection sqlConn = new SqlConnection(getConnectionString);
SqlCommand sqlCmd = new SqlCommand(SqlStr);
SqlDataAdapter sqlDA =new SqlDataAdapter();
DataTable dtSql = new DataTable();
try
{
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = temType;
if (SqlParameters != null)
{
IDictionaryEnumerator hsEnum = SqlParameters.GetEnumerator();
while (hsEnum.MoveNext())
{
sqlCmd.Parameters.AddWithValue(hsEnum.Key.ToString(), hsEnum.Value);
}
}
sqlDA.SelectCommand = sqlCmd;
sqlDA.Fill(dtSql);
return dtSql;
}
catch (Exception exExact)
{
string error = exExact.Message;
throw new Exception(error, exExact);
}
finally
{
sqlConn.Close();
}
}
protected void Button2_Click(object sender, EventArgs e)
{
Hashtable htParam = new Hashtable();
htParam.Add("@Language", "Chi");
htParam.Add("@CurrencyCode", "RMB");
htParam.Add("@CurrencyUnit", "1.0");
htParam.Add("@Region", "42");
string sqlstr = "spr_Channellist";
DataTable mytable = ExecuteDataTable(sqlstr, htParam, CommandType.StoredProcedure);
this.GridView1.DataSource = mytable;
GridView1.DataBind();
}
private String ExecuteDataValue(string SqlStr, Hashtable SqlParameters)
{ String getConnectionString = "Application Name=IPTV;Initial Catalog=IPTVDEVDB;Data Source=10.3.1.218;User ID=sa;password=sa;Pooling=True";
SqlConnection sqlConn = new SqlConnection(getConnectionString);
SqlCommand sqlCmd = new SqlCommand(SqlStr);
string strRtrn ;
try
{
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.Text;
if(SqlParameters != null)
{
IDictionaryEnumerator hsEnum = SqlParameters.GetEnumerator();
while(hsEnum.MoveNext())
{
sqlCmd.Parameters.AddWithValue(hsEnum.Key.ToString(), hsEnum.Value);
}
} strRtrn = Convert.ToString(sqlCmd.ExecuteScalar());
return strRtrn;
}
catch(Exception exExact)
{
string error = exExact.Message;
throw new Exception(error, exExact);
}
finally
{
sqlConn.Close();
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" + Server.MapPath("") + "\\CODEDB.mdb");
string sql = "select * from Code ";
OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet(); oda.Fill(ds);
this.GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
要考虑 SQL 语句的 维护问题的， 最好 语句和 代码 分开，
以上的 办法 可以 执行
select * from ustertable where ID=@id这样的化 我可以 把 语句 存放到 数据库里面
<?xml version="1.0" encoding="gb2312"?>
<Content>
<assembly>
<DefaultExt>.cs</DefaultExt>
<summary>执行MSSQL存储过程的C#代码生成模板</summary>
</assembly>
<Builder>
<Program>
<![CDATA[using System;
using System.Data;
using System.Data.SqlClient;
namespace {namespace}
{
/// <summary>
/// {Statement.Name} 的摘要说明。
/// 对应{Statement.Name}存储过程的参数类
/// </summary>
public class {Statement.Name}
{
#region " {Statement.Name}的成员属性 "
{member}
/// <summary>
/// 存储过程名:{Statement.Name}
/// </summary>
/// <returns></returns>
public string ProcName
{
get
{
return "{Statement.Name}";
}
}
/// <summary>
/// 获取存储过程{Statement.Name}参数集
/// </summary>
public SqlParameter[] SqlParameters
{
get
{
{spinstantiation}
SqlParameter[] parameter = new SqlParameter[] { {spinames} };
return parameter;
}
}
private DataSet _outdataset;
/// <summary>
///存储过程查询输出结果DataSet
/// </summary>
public DataSet OutDataSet
{
get
{
return _outdataset;
}
}
#endregion
#region " {Statement.Name}的存储过程执行方法 "
/// <summary>
/// 执行存储过程{Statement.Name}的ExecuteQuery指令,如有查询结果,则填充到OutDataSet中
/// </summary>
/// <returns>执行过程是否出错,true成功执行,false执行过程出错.</returns>
public bool ExecuteQuery(SqlConnection myConn)
{
bool result = true;
try
{
if (myConn.State == ConnectionState.Closed)
{
myConn.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter(ProcName, myConn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] paras = SqlParameters;
foreach (SqlParameter para in paras)
{
adapter.SelectCommand.Parameters.Add(para);
}
_outdataset = new DataSet();
adapter.Fill(_outdataset);
{output}
}
catch
{
result = false;
}
return result;
}
#endregion
}
}]]>
</Program>
<Foreach>
<Parameters Name="{member}" StrSpace="\r\n">
<![CDATA[
private object _{{{Parameter.Name}}.ToLower()}.Replace("@","");
/// <summary>
/// 存储过程输{{{Parameter.OutPut}}.Replace("true","出")}.Replace("false","入")参数:{Parameter.Name};数据库类型:{Parameter.Type};长度:{Parameter.Length}
/// </summary>
public object {{Parameter.Name}}.Replace("@","")
{
get
{
return _{{{Parameter.Name}}.ToLower()}.Replace("@","");
}
set
{
_{{{Parameter.Name}}.ToLower()}.Replace("@","") = value;
}
}]]>
</Parameters>
<Parameters Name="{spinames}" StrSpace=",">
<![CDATA[sp{{{Parameter.Name}}.ToLower()}.Replace("@","")]]>
</Parameters>
<Parameters Name="{spinstantiation}" StrSpace="\r\n" OutPut="false">
<![CDATA[ SqlParameter sp{{{Parameter.Name}}.ToLower()}.Replace("@","") = new SqlParameter("{Parameter.Name}", SqlDbType.{Parameter.Type}, {Parameter.Length});
sp{{{Parameter.Name}}.ToLower()}.Replace("@","").Value = _{{{Parameter.Name}}.ToLower()}.Replace("@","");]]>
</Parameters>
<Parameters Name="{spinstantiation}" StrSpace="\r\n" OutPut="true">
<![CDATA[ SqlParameter sp{{{Parameter.Name}}.ToLower()}.Replace("@","") = new SqlParameter("{Parameter.Name}", SqlDbType.{Parameter.Type}, {Parameter.Length}, ParameterDirection.Output, false, 0, 0, string.Empty, DataRowVersion.Default, null);
sp{{{Parameter.Name}}.ToLower()}.Replace("@","").Value = _{{{Parameter.Name}}.ToLower()}.Replace("@","");]]>
</Parameters>
<Parameters Name="{output}" StrSpace="\r\n" OutPut="true">
<![CDATA[ _{{{Parameter.Name}}.ToLower()}.Replace("@","") = adapter.SelectCommand.Parameters["{Parameter.Name}"].Value;]]>
</Parameters>
</Foreach>
</Builder>
<DefaultLength>
<bigint>8</bigint>
<binary>50</binary>
<bit>1</bit>
<char>10</char>
<datetime>8</datetime>
<decimal>9</decimal>
<float>8</float>
<image>16</image>
<int>4</int>
<money>8</money>
<nchar>10</nchar>
<ntext>16</ntext>
<nvarchar>50</nvarchar>
<real>4</real>
<smalldatetime>4</smalldatetime>
<smallint>2</smallint>
<smallmoney>4</smallmoney>
<text>16</text>
<timestamp>8</timestamp>
<tinyint>1</tinyint>
<uniqueidentifier>16</uniqueidentifier>
<varbinary>50</varbinary>
<varchar>50</varchar>
</DefaultLength>
<DefaultType>
<bigint>BigInt</bigint>
<binary>Binary</binary>
<bit>Bit</bit>
<char>Char</char>
<datetime>DateTime</datetime>
<decimal>Decimal</decimal>
<float>Float</float>
<image>Image</image>
<int>Int</int>
<money>Money</money>
<nchar>NChar</nchar>
<ntext>NText</ntext>
<nvarchar>NVarChar</nvarchar>
<real>Real</real>
<smalldatetime>SmallDateTime</smalldatetime>
<smallint>SmallInt</smallint>
<smallmoney>SmallMoney</smallmoney>
<text>Text</text>
<timestamp>Timestamp</timestamp>
<tinyint>TinyInt</tinyint>
<uniqueidentifier>UniqueIdentifier</uniqueidentifier>
<varbinary>VarBinary</varbinary>
<varchar>VarChar</varchar>
</DefaultType>
</Content>
那样会简化代码
///// <summary>
///// 获取数据库存储过程参数，并根据传入的参数值为其赋值
///// </summary>
///// <param name="parameters"></param>
protected override List<DbParameter> GetProcedureParameter(string procedureName, Hashtable htParameters)
{
SqlParameter sqlParameter=null;
List<DbParameter> lstDbParameter = new List<DbParameter>();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = (SqlConnection)m_DbConnection;
sqlCmd.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + procedureName + "' order by ORDINAL_POSITION";
SqlDataReader sqlDataReader = null;
try
{
sqlDataReader = sqlCmd.ExecuteReader(); while (sqlDataReader.Read())
{
sqlParameter = new SqlParameter(); sqlParameter.ParameterName = sqlDataReader["PARAMETER_NAME"].ToString().Trim ();
sqlParameter.Direction = sqlDataReader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output; if (htParameters.Contains (sqlParameter.ParameterName)) //检查数据库存储过程所必需的参数是否已提供
{
switch (sqlDataReader["DATA_TYPE"].ToString().Trim ().ToLower ()) //其中缺少：sql_variant类型，此类型在Net中无对应类型
{
case "bigint":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToInt64(htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.BigInt;
break;
case "binary":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = (System.Byte[])htParameters[sqlParameter.ParameterName];
}
sqlParameter.Size = (int)sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.Binary;
break;
case "bit":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToBoolean(htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Bit;
break;
case "char":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToString(htParameters[sqlParameter.ParameterName]);
}
sqlParameter.Size = (System.Int32 )sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.Char;
break;
case "datetime":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToDateTime (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.DateTime;
break;
case "decimal":
case "numeric":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToDecimal (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Decimal;
sqlParameter.Precision = (System.Byte)sqlDataReader["NUMERIC_PRECISION"];
sqlParameter.Scale = Convert.ToByte (sqlDataReader["NUMERIC_SCALE"]);
break;
case "float":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToDouble(htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Float;
break;
case "image":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = (System.Byte[])htParameters[sqlParameter.ParameterName];
}
sqlParameter.SqlDbType = SqlDbType.Image;
break;
case "int":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToInt32 (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Int;
break;
case "money":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToDecimal (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Money;
break;
case "nchar":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToString (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.Size = (int)sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.NChar;
break;
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToString (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.NText ;
break;
case "nvarchar":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToString (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.Size = (int)sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.NVarChar;
break;
case "real":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToSingle (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Real;
break;
case "smalldatetime":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToDateTime (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.SmallDateTime;
break;
case "smallint":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value =Convert.ToInt16 (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.SmallInt;
break;
case "smallmoney":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToDecimal (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.SmallMoney;
break;
case "text":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToString (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.Text;
break;
case "timestamp":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = (System.Byte[])htParameters[sqlParameter.ParameterName];
}
sqlParameter.SqlDbType = SqlDbType.Timestamp;
break;
case "tinyint":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToByte (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.SqlDbType = SqlDbType.TinyInt;
break;
case "uniqueidentifier":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = (System.Guid)htParameters[sqlParameter.ParameterName];
}
sqlParameter.SqlDbType = SqlDbType.UniqueIdentifier;
break;
case "varbinary":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = (System.Byte[])htParameters[sqlParameter.ParameterName];
}
sqlParameter.Size = (int)sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.VarBinary;
break;
case "varchar":
if (sqlParameter.Direction == ParameterDirection.Input)
{
sqlParameter.Value = Convert.ToString (htParameters[sqlParameter.ParameterName]);
}
sqlParameter.Size = (int)sqlDataReader["CHARACTER_MAXIMUM_LENGTH"];
sqlParameter.SqlDbType = SqlDbType.VarChar;
break;
}
}
else //如果参数不存在，则说明赋参时出错，直接返回null
{
return null;
}
lstDbParameter.Add(sqlParameter);
}
return lstDbParameter;
}
catch(Exception e)
{
string s = sqlParameter.ParameterName;
string em = e.Message;
return null;
}
finally
{
if (sqlDataReader != null)
{
sqlDataReader.Close();
sqlDataReader.Dispose();
sqlDataReader = null;
}
if (sqlCmd != null)
{
sqlCmd.Dispose();
sqlCmd = null;
}
}
}