ASP.NET DataBase
21738 ワード
//
public class DataBase
{
private SqlConnection conn;//
#region
private void Open()
{
if (conn == null)
{
conn = new SqlConnection();
//conn.ConnectionString = ConfigurationSettings.AppSettings["connString"].ToString();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ToString();
conn.Open();
}
else
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
}
}
#endregion
#region
private void Close()
{
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
#endregion
#region
public void Dispose()
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
#endregion
#region SqlParameter
/// <summary>
///
/// </summary>
/// <param name="ParamName"> </param>
/// <param name="DbType"> </param></param>
/// <param name="Size"> </param>
/// <param name="Value"> </param>
/// <returns> parameter </returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, Object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
///
/// </summary>
/// <param name="ParamName"> </param>
/// <param name="DbType"> </param>
/// <param name="Size"> </param>
/// <param name="Direction"> </param>
/// <param name="Value"> </param>
/// <returns> parameter </returns>
private SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size>0)
{
param = new SqlParameter(ParamName, DbType, Size);
}
else
{
param = new SqlParameter(ParamName, DbType);
}
param.Direction = Direction;
if (!(param.Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
#endregion
#region ( )
/// <summary>
///
/// </summary>
/// <param name="procName"> </param>
/// <param name="prams"> </param>
/// <returns></returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
//
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// SQL
/// </summary>
/// <param name="procName"> </param>
/// <returns></returns>
public int RunProc(string procName)
{
this.Open();
SqlCommand cmd = new SqlCommand(procName, conn);
int num = cmd.ExecuteNonQuery();
this.Close();
return num;
}
#endregion
#region ( )
/// <summary>
/// , DataSet
/// </summary>
/// <param name="procName"> </param>
/// <param name="prams"> </param>
/// <param name="tbName"> </param>
/// <returns></returns>
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
{
SqlDataAdapter dap = CreateDataAdapter(procName, prams);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.Close();
return ds;
}
/// <summary>
/// , DataSet
/// </summary>
/// <param name="procName"> </param>
/// <param name="tbName"> </param>
/// <returns>DataSet</returns>
public DataSet RunProcReturn(string procName, string tbName)
{
SqlDataAdapter dap = CreateDataAdapter(procName, null);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
return ds;
}
#endregion
#region SqlDataAdapter
/// <summary>
/// SqlDataAdapter
/// </summary>
/// <param name="procName"> </param>
/// <param name="prams"> </param>
/// <returns></returns>
private SqlDataAdapter CreateDataAdapter(string procName, SqlParameter[] prams)
{
this.Open();
SqlDataAdapter dap = new SqlDataAdapter(procName, conn);
dap.SelectCommand.CommandType = CommandType.Text;// :
if (prams != null)
{
foreach (SqlParameter param in prams)
{
dap.SelectCommand.Parameters.Add(param);//
}
}
//
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
String.Empty, DataRowVersion.Default, null));
return dap;
}
#endregion
#region SqlCommand
/// <summary>
/// SqlCommand
/// </summary>
/// <param name="procName"> </param>
/// <param name="prams" </param>
/// <returns> SqlCommand </returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
this.Open();
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.Text;// :
//
if (prams != null)
{
foreach (SqlParameter param in prams)
{
cmd.Parameters.Add(param);
}
}
//
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}
#endregion
}