private static void OpenCon()
{
/// <summary>
/// 打开数据库连接
/// </summary>
if(Con.State == ConnectionState.Closed)
{
Con.ConnectionString = ConfigurationSettings.AppSettings["dsn"].ToString().Trim(); ;
SqlCmd.Connection =Con;
try
{
Con.Open();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void CloseCon()
{
if(Con.State == ConnectionState.Open)
{
Con.Close();
}
Con.Dispose();
SqlCmd.Dispose();
}
public static DataTable ReadXml(XmlDocument doc)
{
XmlNodeList NoLst = doc.SelectNodes("//Dept/Item");
DataTable dt = new DataTable("XmlDept");
DataColumn dc;
DataRow dr;
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int32");
dc.ColumnName = "id";
dc.ReadOnly = true;
dc.Unique = true;
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Item";
dc.AutoIncrement = false;
dc.Caption = "Item";
dc.ReadOnly = false;
dc.Unique = false;
dt.Columns.Add(dc);
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = dt.Columns["id"];
dt.PrimaryKey = PrimaryKeyColumns;
for(int i=0;i<NoLst.Count;i++)
{
dr = dt.NewRow();
dr["id"] = i;
dr["Item"] = NoLst[i].InnerText;
dt.Rows.Add(dr);
}
return dt;
}
/// <summary>
/// 创建存储过程的参数
/// </summary>
/// <param name="PerName">存储过程所需参数的名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的parameter对象</returns>
public SqlParameter CreatePer(string PerName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter per;
///当参数大小为0时,不使用该参数大小值
if(Size > 0)
{
per = new SqlParameter(PerName,DbType,Size);
}
else
{
///当参数大小为0时,不使用该参数大小值
per = new SqlParameter(PerName,DbType);
}
///创建输出类型的参数
per.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
per.Value = Value;
}
///返回创建的参数
return per;
}
/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="PerName">存储过程所需参数的名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter CreateInPer(string PerName, SqlDbType DbType, int Size, object Value)
{
return CreatePer(PerName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="PerName">存储过程所需参数的名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的parameter 对象</returns>
public SqlParameter CreateOutPer(string PerName, SqlDbType DbType, int Size)
{
return CreatePer(PerName, DbType, Size, ParameterDirection.Output, null);
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter CreateReturnPer(string PerName, SqlDbType DbType, int Size)
{
return CreatePer(PerName, DbType, Size, ParameterDirection.ReturnValue, null);
}
/// <summary>
/// 创建执行数据库操作的Command对象并添加Command对象执行操作过程中所需要的参数
/// </summary>
/// <param name="ProcName">存储过程的名称</param>
/// <param name="Prams">存储过程所需要的参数集合</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string ProcName,SqlParameter[] Pers)
{
OpenCon();
SqlCommand Cmd = new SqlCommand(ProcName,Con);
Cmd.CommandType = CommandType.StoredProcedure;
if(Pers != null)
{
foreach(SqlParameter per in Pers)
{
Cmd.Parameters.Add(per);
}
}
Cmd.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
return Cmd;
}
/// <summary>
/// 执行不带任何参数的存储过程
/// </summary>
/// <param name="ProcName">存储过程的名称</param>
/// <returns>返回存储过程的返回值</returns>
public int RunProc(string ProcName)
{
SqlCommand Cmd = CreateCommand(ProcName,null);
try
{
Cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
// SystemError.SystemLog(ex.Message);
string a = ex.Message;
}
CloseCon();
return (int)Cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// 执行带有一个参数集合的存储过程
/// </summary>
/// <param name="ProcName">存储过程的名称</param>
/// <param name="Prams">存储过程所需要的参数集合</param>
/// <returns>返回存储过程的返回值</returns>
public int RunProc(string ProcName,SqlParameter[] Prams)
{
SqlCommand Cmd = CreateCommand(ProcName,Prams);
try
{
Cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
// SystemError.SystemLog(ex.Message);
string a = ex.Message;
}
CloseCon();
return (int)Cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// 执行一个不带任何参数的存储过程,但该函数带有一个输出类型的参数,该参数用于保存从数据库获取的结果。
/// </summary>
/// <param name="ProcName">存储过程的名称</param>
/// <param name="dataReader">返回存储过程的返回值</param>
public void RunProc(string ProcName,out SqlDataReader dataReader)
{
SqlCommand Cmd = CreateCommand(ProcName,null);
dataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行带有一个参数集合的存储过程,该函数带有一个输出类型的参数,该参数用于保存从数据库获取的结果。
/// </summary>
/// <param name="ProcName">存储过程的名称</param>
/// <param name="prams">存储过程所需要的参数集合</param>
/// <param name="dataReader">返回存储过程的返回值</param>
public void RunProc(string ProcName,SqlParameter[] pers,out SqlDataReader dataReader)
{
SqlCommand Cmd = CreateCommand(ProcName,pers);
dataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
感觉还有不理想的地方,请过客多多指教!