public abstract class DBHelper
{
public static string connectionString = @"DATA SOURCE=.\sqldata;UID=sa;PWD=bkin123;DATABASE=bkintest";//ConfigurationManager.ConnectionStrings["database"].ConnectionString;
public DBHelper()
{
//connectionString = @"DATA SOURCE=(local);UID=sa;PWD=sa;DATABASE=db";
} #region 不带参数
//执行不带参数数据记录的增、删、改操作，并返回执行后的结果值
public static int ExecuteCommand(string safeSql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} //返回执行不带参数SQL语句后中结果集的第一行第一列
public static object GetScalar(string safeSql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
object result = cmd.ExecuteScalar();
return result;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} //返回执行不带参数SQL语句后的结果数据表
public static DataTable GetDataTable(string safeSql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[];
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
#endregion #region 带参数
//执行带参数数据记录的增、删、改操作，返回执行后的结果值
public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} //返回执行带参数SQL语句后中结果集的第一行第一列
public static object GetScalar(string safeSql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
cmd.Parameters.AddRange(values);
object result = cmd.ExecuteScalar();
return result;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} //返回执行带参数SQL语句后的结果数据表
public static DataTable GetDataTable(string safeSql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(safeSql, connection))
{
try
{
connection.Open();
DataSet ds = new DataSet();
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[];
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
#endregion
}