增删改,已经写好了,但是还不知道如何写查询语句,返回DataTable
请各位帮下忙,小弟上班不久,还没用过存储语句 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{ //Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open(); //Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType; //Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans; // Bind the parameters passed in
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
public static string ExecuteSql(CommandType commandType, String strSql, params SqlParameter[] commandParameters)
{
SqlConnection cn = Connection;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, strSql, commandParameters);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return "1";
}
catch (Exception e)
{
return e.Message;
}
finally
{
Connection.Close();
}
}
请各位帮下忙,小弟上班不久,还没用过存储语句 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{ //Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open(); //Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType; //Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans; // Bind the parameters passed in
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
public static string ExecuteSql(CommandType commandType, String strSql, params SqlParameter[] commandParameters)
{
SqlConnection cn = Connection;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, strSql, commandParameters);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return "1";
}
catch (Exception e)
{
return e.Message;
}
finally
{
Connection.Close();
}
}
DataSet 函数名(string sql)
或者
DataTable 函数名(string sql)
之类的函数。或者你可以下载一个微软标准的 dbhelper。
/// <summary>
/// 绑定热销产品的类别HotProductKind
/// </summary>
/// <param name="">不需要参数</param>
/// <returns>返回热销产品类别</returns>
public static DataTable gethotproductkind()
{
SqlCommand cmd = new SqlCommand("HotProductKind",Con);
cmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
#endregion
create proc HotProductKind
as
begin
select * from Test;
end