数据库操作类
1 /// <summary>
2 /// 数据库连接字符串
3 /// </summary>
4 private static string sqlstr = System.Configuration.ConfigurationSettings.AppSettings["mii_db_ConnStr"].ToString();
1 /// <summary>
2 /// 数据库连接对象
3 /// </summary>
4 private static SqlConnection sqlconn = new SqlConnection(sqlstr);
1 /// <summary>
2 /// 创建SqlConnection连接对象
3 /// </summary>
4 /// <returns>返回连接对象</returns>
5 private static void SqlOpen()
6 {
7 sqlconn.Open();
8 }
9
10 /// <summary>
11 /// 关闭数据库连接
12 /// </summary>
13 private static void SqlClose()
14 {
15 sqlconn.Close();
16 }
public static int ExecuteProcedure(string sql, SqlParameter[] sqlpara)
{
lock (sqlconn)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = sqlconn;
foreach (SqlParameter para in sqlpara)
{
cmd.Parameters.Add(para);
}
SqlOpen();
try
{
return cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
SqlClose();
}
}
}
/// <summary>
/// 数据库表查询
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="sqlpara">SQL参数</param>
/// <returns>返回查询结果DataTable类型</returns>
public static DataTable SelectTable(string sql, SqlParameter[] sqlpara)
{
DataTable dt = new DataTable();
lock (sqlconn)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = sql;
cmd.Connection = sqlconn;
foreach (SqlParameter para in sqlpara)
{
cmd.Parameters.Add(para);
}
SqlOpen();
da.SelectCommand = cmd;
try
{
da.Fill(dt);
}
catch (InvalidOperationException ex)
{
throw ex;
}
finally
{
SqlClose();
}
}
return dt;
}
/// <summary>
/// 数据库表查询
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回查询结果DataTable类型</returns>
public static DataTable SelectTable(string sql)
{
DataTable dt = new DataTable();
lock (sqlconn)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, sqlconn);
SqlOpen();
try
{
da.Fill(dt);
}
catch (InvalidOperationException ex)
{
throw ex;
}
finally
{
SqlClose();
}
}
return dt;
}
/// <summary>
/// 更新表信息
/// </summary>
/// <param name="sql">更新语句</param>
/// <param name="sqlpara">更新表所需要的参数</param>
/// <returns>返回影响的行数，如果返回-1则证明更新失败</returns>
public static int UpdateTable(string sql, SqlParameter[] sqlpara)
{
int re_value = 0;
lock (sqlconn)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = sqlconn;
foreach (SqlParameter para in sqlpara)
{
cmd.Parameters.Add(para);
}
da.UpdateCommand = cmd;
SqlOpen();
try
{
re_value = da.UpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
re_value = -1;
throw ex;
}
finally
{
SqlClose();
}
}
return re_value;
}
/// <summary>
/// 向表中插入数据
/// </summary>
/// <param name="sql">插入语句</param>
/// <param name="sqlpara">插入行所需的参数</param>
/// <returns>返回影响的行数，如果返回-1则证明插入失败</returns>
public static int InsertTable(string sql, SqlParameter[] sqlpara)
{
int re_value = 0;
lock (sqlconn)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = sqlconn;
foreach (SqlParameter para in sqlpara)
{
cmd.Parameters.Add(para);
}
SqlOpen();
da.InsertCommand = cmd;
try
{
re_value = da.InsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
re_value = -1;
throw ex;
}
finally
{
SqlClose();
}
}
return re_value;
}
/// <summary>
/// 删除表中的数据
/// </summary>
/// <param name="sql">删除语句</param>
/// <param name="sqlpara">删除语句所需要的参数</param>
/// <returns>返回影响的行数，如果返回结果为-1则表示删除失败</returns>
public static int DeleteTable(string sql, SqlParameter[] sqlpara)
{
int re_value = 0;
lock (sqlconn)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = sqlconn;
foreach (SqlParameter para in sqlpara)
{
cmd.Parameters.Add(para);
}
SqlOpen();
da.DeleteCommand = cmd;
try
{
re_value = da.DeleteCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
re_value = -1;
throw ex;
}
finally
{
SqlClose();
}
}
return re_value;
}
/// <summary>
/// 删除表中的数据
/// </summary>
/// <param name="sql">删除语句</param>
/// <returns>返回影响的行数，如果返回结果为-1则表示删除失败</returns>
public static int DeleteTable(string sql)
{
int re_value = 0;
lock (sqlconn)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = sqlconn;
SqlOpen();
da.DeleteCommand = cmd;
try
{
re_value = da.DeleteCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
re_value = -1;
throw ex;
}
finally
{
SqlClose();
}
}
return re_value;
}
public static object ConvertNull(string value)
{
if (value==null)
{
return System.Data.SqlTypes.SqlString.Null;
}
return value;
}
public static object ConvertNull(System.DateTime value)
{
if (value==System.DateTime.MinValue)
{
return System.Data.SqlTypes.SqlDateTime.MinValue;
}
return value;
}
public static object ConvertNull(bool value)
{
if (value)
{
return System.Data.SqlTypes.SqlBoolean.True;
}
else
{
return System.Data.SqlTypes.SqlBoolean.False;
}
}
/// <summary>
/// 获得单个值
/// </summary>
/// <param name="sql"></param>
/// <param name="pre"></param>
/// <returns></returns>
public static object SetExecuteScalar(string sql, CommandType ct, params SqlParameter[] pre)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = ct;
cmd.CommandText = sql;
cmd.Connection = sqlconn;
object obj = null;
foreach (SqlParameter sp in pre)
{
cmd.Parameters.Add(sp);
}
try
{
SqlOpen();
obj = cmd.ExecuteScalar();
}
catch
{
obj = null;
}
finally
{
SqlClose();
}
return obj;
}
因为是用VS2010写的 ，格式有些没有对齐。不过还是可以参考的。
