如何写带参数的函数(如对数据的增删改查等操作)
/// <summary>
/// 插入记录
/// </summary>
/// <param name= "connString "> 连接数据库 </param>
/// <param name= "cmdType "> Sql语句类型 </param>
/// <param name= "str_Sql "> Sql语句 </param>
/// <param name= "ht "> 表示层传递过来的哈希表对象 </param>
public static void Insert(string connString,string TableName,Hashtable ht)
{
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et=ht.GetEnumerator();
int i=0;
//作哈希表循环
while(et.MoveNext())
{
System.Data.SqlClient.SqlParameter sp=Data.MakeParam( "@ "+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp;//添加SqlParameter对象
i=i+1;
}
string str_Sql=GetInsertSqlbyHt(TableName,ht); // 获得插入sql语句
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
}
/// <summary>
/// 执行ExecuteNonQuery
/// </summary>
/// <param name= "connString "> 数据库连接 </param>
/// <param name= "cmdType "> Sql语句类型 </param>
/// <param name= "cmdText "> Sql语句 </param>
/// <param name= "cmdParms "> Parm数组 </param>
/// <returns> 返回影响行数 </returns>
public static int ExecuteNonQuery(string connString , CommandType cmdType, string cmdText ,params SqlParameter[] cmdParms)
{
SqlCommand cmd=new SqlCommand();
using (SqlConnection conn=new SqlConnection(connString))
{
conn.Open();
cmd.Connection=conn;
cmd.CommandText=cmdText;
if(cmdParms!=null)
{
foreach(SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
return val;
}
}
如上面代码,我如何知道这个函数需要什么数据库连接呀、表呀、哈希表等等之类的参数？
[解决办法]
其实上面的代码很好理解...
1.数据库连接==根据你的project来定的，你可以拉一个sqlDataAdapter 配置一下，就可以取到数据库连接了...
2,表，你可以在数据库建
3，哈希表： 上面的代码只是用哈希表取一个组参数，一般情况下是因为参数太多或是参数个数不定是使用参数数组。你可以做简单一点。
代码：
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace EIP.Class
{
class DataAccess
{
public static string connectionString = ConfigurationManager.AppSettings[ "cnnStr "];
private SqlConnection cnn = new SqlConnection(connectionString);
#region 读取DataSet
public DataSet ExecuteSql(string sqlstr)
{
//throw new System.NotImplementedException();
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr,cnn);
try
{
cnn.Open();
adapter.Fill(ds);
}
catch (Exception ex)
{
throw new Exception( "数据读取出错！ ",ex);
}
finally
{
cnn.Close();
}
return ds;
}
#endregion
#region 读取DataTable
public DataTable DataTableExecuteSql(string sqlstr)
{
//throw new System.NotImplementedException();
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr,cnn);
try
{
cnn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
throw new Exception( "数据读取出错！ ",ex);
}
finally
{
cnn.Close();
}
return dt;
}
#endregion
#region 执行SQL指令
public void ExecuteSql(SqlCommand sqlCmd)
{
//throw new System.NotImplementedException();
sqlCmd.Connection = this.cnn;
try
{
cnn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception( "数据更新出错！ ", ex);
}
finally
{
cnn.Close();
}
}
#endregion
#region 取某个栏位的值
public string GetString(string sqlstr)
{
try
{
cnn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlstr, cnn);
object str=sqlCmd.ExecuteScalar();
return Convert.ToString(str);
}
catch (Exception ex)
{
throw new Exception( "数据更新出错！ ", ex);
}
finally
{
cnn.Close();
}
}
#endregion
#region 取表中识别种子的下一个编号
public Int64 GetNextID(string table)
{
Int64 newProdID = 0;
try
{
string sqlstring = "SELECT IDENT_CURRENT(@table) + (SELECT IDENT_INCR(@table)) ";
SqlCommand sqlcmd = new SqlCommand(sqlstring, this.cnn);
sqlcmd.Parameters.Add(new SqlParameter( "@table ", table));
cnn.Open();
newProdID = Convert.ToInt64(sqlcmd.ExecuteScalar());
}
catch (Exception ex)
{
MessageBox.Show( "此表不存在！ "+ex.Message, "系统提示 ",MessageBoxButtons.OK ,MessageBoxIcon.Warning );
}
finally
{
cnn.Close();
}
return newProdID;
}
#endregion
public void UpdateDataWithTransaction(DataSet ds,SqlDataAdapter masterDataAdapter,SqlDataAdapter detailDataAdapter)
{
SqlTransaction trans = null;
try
{
masterDataAdapter.SelectCommand.Connection = cnn;
masterDataAdapter.InsertCommand.Connection = cnn;
masterDataAdapter.UpdateCommand.Connection = cnn;
masterDataAdapter.DeleteCommand.Connection = cnn;
detailDataAdapter.SelectCommand.Connection = cnn;
detailDataAdapter.InsertCommand.Connection = cnn;
detailDataAdapter.UpdateCommand.Connection = cnn;
detailDataAdapter.DeleteCommand.Connection = cnn;
cnn.Open();
trans = cnn.BeginTransaction();
masterDataAdapter.DeleteCommand.Transaction = trans;
masterDataAdapter.InsertCommand.Transaction = trans;
masterDataAdapter.UpdateCommand.Transaction = trans;
detailDataAdapter.DeleteCommand.Transaction = trans;
detailDataAdapter.InsertCommand.Transaction = trans;
detailDataAdapter.UpdateCommand.Transaction = trans;
masterDataAdapter.Update(ds.Tables[ "Master "].Select( " ", " ", DataViewRowState.Added));
detailDataAdapter.Update(ds.Tables[ "Detail "].Select( " ", " ", DataViewRowState.Added));
masterDataAdapter.Update(ds.Tables[ "Master "].Select( " ", " ", DataViewRowState.ModifiedCurrent));
detailDataAdapter.Update(ds.Tables[ "Detail "].Select( " ", " ", DataViewRowState.ModifiedCurrent));
detailDataAdapter.Update(ds.Tables[ "Detail "].Select( " ", " ", DataViewRowState.Deleted));
masterDataAdapter.Update(ds.Tables[ "Master "].Select( " ", " ", DataViewRowState.Deleted));
trans.Commit();
}
catch(Exception ex)
{
trans.Rollback();
throw new Exception( "更新数据出错 ",ex);
}
finally
{
if (cnn.State != ConnectionState.Closed)
cnn.Close();
}
}
}
}
