using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using JCDG.Data;namespace JCDG.SqlAccess
{
public class SqlHelper
{
private SqlConnection myConnection; /// <summary>
/// 打开数据库
/// </summary>
private void Open()
{
if (myConnection == null)
{
myConnection = new SqlConnection(Constant.connsring);
}
if (myConnection.State == ConnectionState.Closed)
{
try
{
myConnection.Open();
}
catch (SqlException ex)
{
SystemError.SystemLog(ex.Message);
}
}
} /// <summary>
/// 关闭数据的连接
/// </summary>
private void Close()
{
if (myConnection != null)
{
if (myConnection.State == ConnectionState.Open)
myConnection.Close();
}
} /// <summary>
/// 释放与数据库的连接
/// </summary>
private void Dispose()
{
if (myConnection != null)
{
myConnection.Dispose();
myConnection = null;
}
} /// <summary>
/// 生成存储过程时的参数
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="DbType">参数类型</param>
/// <param name="Direction">存储方向</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
private SqlParameter CreateParam(string ParamName, SqlDbType DbType, ParameterDirection Direction, object Value)
{
SqlParameter myParameter = new SqlParameter(ParamName, DbType);
myParameter.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
myParameter.Value = Value;
myParameter.Size = 1024;
return myParameter;
} /// <summary>
/// 创建Input型存储过程参数
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="DbTybe">参数类型</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
public SqlParameter CreateInParam(string ParamName, SqlDbType DbTybe, object Value)
{
return CreateParam(ParamName, DbTybe, ParameterDirection.Input, Value);
}
public SqlParameter CreateOutParam(string ParamName, SqlDbType DbTybe)
{
return CreateParam(ParamName, DbTybe, ParameterDirection.Output, null);
}
/// <summary>
/// 执行存储过程命令
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">所需参数</param>
/// <returns></returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
Open();
SqlCommand myCommand = new SqlCommand(procName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
myCommand.Parameters.Add(parameter);
}
}
return myCommand;
} public void RunPro(string procname, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(procname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
myAdapter.Fill(myTable);
Close();
}
public void RunPro(string proname, SqlParameter prams, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(proname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
if(prams!=null)
{
myAdapter.SelectCommand.Parameters.Add(prams);
}
myAdapter.Fill(myTable);
Close();
} public void RunPro(string proname, SqlParameter[] prams, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(proname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter pram in prams)
{
myAdapter.SelectCommand.Parameters.Add(pram);
}
myAdapter.Fill(myTable);
Close();
} public void RunPro(string proname, SqlParameter[] prams)
{
SqlCommand myCommand = CreateCommand(proname, prams);
myCommand.ExecuteNonQuery();
} }
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using JCDG.Data;namespace JCDG.SqlAccess
{
public class SqlHelper
{
private SqlConnection myConnection; /// <summary>
/// 打开数据库
/// </summary>
private void Open()
{
if (myConnection == null)
{
myConnection = new SqlConnection(Constant.connsring);
}
if (myConnection.State == ConnectionState.Closed)
{
try
{
myConnection.Open();
}
catch (SqlException ex)
{
SystemError.SystemLog(ex.Message);
}
}
} /// <summary>
/// 关闭数据的连接
/// </summary>
private void Close()
{
if (myConnection != null)
{
if (myConnection.State == ConnectionState.Open)
myConnection.Close();
}
} /// <summary>
/// 释放与数据库的连接
/// </summary>
private void Dispose()
{
if (myConnection != null)
{
myConnection.Dispose();
myConnection = null;
}
} /// <summary>
/// 生成存储过程时的参数
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="DbType">参数类型</param>
/// <param name="Direction">存储方向</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
private SqlParameter CreateParam(string ParamName, SqlDbType DbType, ParameterDirection Direction, object Value)
{
SqlParameter myParameter = new SqlParameter(ParamName, DbType);
myParameter.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
myParameter.Value = Value;
myParameter.Size = 1024;
return myParameter;
} /// <summary>
/// 创建Input型存储过程参数
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="DbTybe">参数类型</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
public SqlParameter CreateInParam(string ParamName, SqlDbType DbTybe, object Value)
{
return CreateParam(ParamName, DbTybe, ParameterDirection.Input, Value);
}
public SqlParameter CreateOutParam(string ParamName, SqlDbType DbTybe)
{
return CreateParam(ParamName, DbTybe, ParameterDirection.Output, null);
}
/// <summary>
/// 执行存储过程命令
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">所需参数</param>
/// <returns></returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
Open();
SqlCommand myCommand = new SqlCommand(procName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
myCommand.Parameters.Add(parameter);
}
}
return myCommand;
} public void RunPro(string procname, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(procname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
myAdapter.Fill(myTable);
Close();
}
public void RunPro(string proname, SqlParameter prams, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(proname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
if(prams!=null)
{
myAdapter.SelectCommand.Parameters.Add(prams);
}
myAdapter.Fill(myTable);
Close();
} public void RunPro(string proname, SqlParameter[] prams, ref DataTable myTable)
{
Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(proname, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter pram in prams)
{
myAdapter.SelectCommand.Parameters.Add(pram);
}
myAdapter.Fill(myTable);
Close();
} public void RunPro(string proname, SqlParameter[] prams)
{
SqlCommand myCommand = CreateCommand(proname, prams);
myCommand.ExecuteNonQuery();
} }
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货