public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString))
{
//通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery(); //清空SqlCommand中的参数列表
cmd.Parameters.Clear();
return val;
}
}
在这个方法中,PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
cmd从哪来的,null是空,但是他代表什么,如果说对应上面的话,应该是string connectionString对应conn,CommandType cmdType对应cmdType,但是cmd对应什么,null又对应什么,还是说我理解说了,应该是怎么样,请高手指教
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString))
{
//通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery(); //清空SqlCommand中的参数列表
cmd.Parameters.Clear();
return val;
}
}
在这个方法中,PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
cmd从哪来的,null是空,但是他代表什么,如果说对应上面的话,应该是string connectionString对应conn,CommandType cmdType对应cmdType,但是cmd对应什么,null又对应什么,还是说我理解说了,应该是怎么样,请高手指教
就是这个cmd啊查看下这个方法具体怎么写的就知道了
http://download.csdn.net/source/2052993
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;namespace Office.DAL
{
/// <summary>
/// 数据库数据访问层的通用类
/// </summary>
public class Helper
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["url"].ConnectionString.ToString();
private static SqlConnection connection = null;
private static SqlDataAdapter adapter = null;
private static SqlCommand command = null; /// <summary>
/// 查询返回第一行第一列的数据
/// </summary>
/// <param name="commandType">命令类型(存储过程/T-SQL)</param>
/// <param name="commandText">T-SQL</param>
/// <param name="commandParameters">参数</param>
/// <returns>第一行第一列的数据</returns>
private static int ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
command = new SqlCommand();
using (connection = new SqlConnection(connectionString))
{
PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
int result = (int)command.ExecuteScalar();
command.Parameters.Clear();
return result;
}
} private static DataTable ExecuteTable(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
command = new SqlCommand();
using (connection = new SqlConnection(connectionString))
{
PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
command.Parameters.Clear();
return table;
}
}
/// <summary>
/// T-SQL无参查询
/// </summary>
/// <param name="commandText">T-SQL</param>
/// <returns>数据集</returns>
private static DataTable ExecuteTable(string commandText)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString))
{
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
}
private static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
command = new SqlCommand();
connection = new SqlConnection(connectionString);
try
{
PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
SqlDataReader reader = command.ExecuteReader();
command.Parameters.Clear();
return reader;
}
catch (Exception)
{
connection.Close();
throw;
}
}
#region 执行的存储过程或脚本
/// <summary>
/// 执行没有返回值的存储过程或脚本
/// </summary>
/// <param name="commandType">执行的类型</param>
/// <param name="commandText">存储过程的名称或T-SQL语句</param>
/// <param name="commandParameters">执行参数</param>
/// <returns>受影响的行数</returns>
private static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
command = new SqlCommand();
using (connection = new SqlConnection(connectionString))
{
PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
int result = command.ExecuteNonQuery();
command.Parameters.Clear();
return result;
}
}
#endregion; #region 执行的存储过程 事务
/// <summary>
/// 执行的存储过程
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>受影响的行数</returns>
public static int GetNonQueryProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.StoredProcedure, StoredProcedureName, commandParameters);
}
#endregion
{
SqlCommand command = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
int result = command.ExecuteNonQuery();
command.Parameters.Clear();
return result;
}
} #region 执行的存储过程 事务
/// <summary>
/// 执行的存储过程或脚本 事务
/// </summary>
/// <param name="trans">现有的事务</param>
/// <param name="commandType">执行的类型</param>
/// <param name="StoredProcedureName">存储过程的名称</param>
/// <param name="commandParameters">执行参数</param>
/// <returns>所影响的行数</returns>
public static int GetExecuteNonQuery(SqlTransaction transaction, string StoredProcedureName, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, StoredProcedureName, commandParameters);
}
#endregion #region 关闭数据库的链接
public static void CloseConnection()
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
#endregion;
#region 执行的T-SQL脚本
/// <summary>
/// 执行的T-SQL脚本
/// </summary>
/// <param name="sql">执行的T-SQL脚本</param>
/// <param name="commandParameters">参数</param>
/// <returns>受影响的行数</returns>
public static int GetNonQuerySql(string sql, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.Text, sql, commandParameters);
}
#endregion
#region 执行存储过程,返回一个DataReader
/// <summary>
/// 执行存储过程 返回一个dataReader
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <param name="commandParameters">参数列表</param>
/// <returns>DataReader</returns>
public static SqlDataReader GetExecuteProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return ExecuteReader(CommandType.StoredProcedure, StoredProcedureName, commandParameters);
}
#endregion #region 执行T-SQL,返回一个DataReader
/// <summary>
/// 执行T-SQL 返回一个DataReader
/// </summary>
/// <param name="sql">T-SQL</param>
/// <param name="commandParameters">参数列表</param>
/// <returns>Datareader</returns>
public static SqlDataReader GetExecuteSql(string sql, params SqlParameter[] commandParameters)
{
return ExecuteReader(CommandType.Text, sql, commandParameters);
}
#endregion
#region 执行T-SQL,返回一个DataTable
/// <summary>
/// 执行T-SQL 返回DataTable
/// </summary>
/// <param name="sql">执行的T-SQL语句</param>
/// <param name="commandParameters">参数列表</param>
/// <returns>DataTable</returns>
public static DataTable GetTableSql(string sql, params SqlParameter[] commandParameters)
{
return ExecuteTable(CommandType.Text,sql,commandParameters);
}
#endregion; #region 执行存储过程,返回一个DataTable
/// <summary>
/// 执行存储过程 返回一个DataTable
/// </summary>
/// <param name="StoredProcedureName">存储过程名</param>
/// <param name="commandParameters">参数列表</param>
/// <returns>DataTable</returns>
public static DataTable GetTableProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return ExecuteTable(CommandType.StoredProcedure, StoredProcedureName, commandParameters);
}
#endregion; #region 执行存储过程返回的第一列的第一个记录
/// <summary>
/// 执行存储过程或T-SQL语句返回的第一列的第一个记录
/// </summary>
/// <param name="connectionString">现有的数据库连接</param>
/// <param name="commandType">执行的类型</param>
/// <param name="StoredProcedureName">存储过程的名称</param>
/// <param name="commandParameters">执行参数</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static int GetScalarProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return ExecuteScalar(CommandType.StoredProcedure, StoredProcedureName, commandParameters);
}
#endregion #region 执行T-SQL返回第一行第一列的记录
/// <summary>
/// 执行T-SQL返回第一行第一列的记录
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="commandParameters">参数列表</param>
/// <returns>第一行第一列的第一条记录</returns>
public static int GetScalarSql(string sql, params SqlParameter[] commandParameters)
{
return ExecuteScalar(CommandType.Text, sql, commandParameters);
}
#endregion; #region 创建一个SqlCommand对象以此来执行存储过程
/// <summary>
/// 创建一个SqlCommand对象以此来执行存储过程
/// </summary>
/// <param name="command">qlCommand object</param>
/// <param name="connection">SqlConnection</param>
/// <param name="transation">SqlTransaction</param>
/// <param name="commandType">command type e.g. stored procedure or text</param>
/// <param name="commandText">command text, e.g. select * from table</param>
/// <param name="commandParameters">SqlParameters to use in the command</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transation, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
if (transation != null)
{
command.Transaction = transation;
}
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
{
command.Parameters.Add(parm);
}
}
}
#endregion
}
}