SqlHelper

public class SqlHelper
{
//读取连接字符串
private static string ConStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 自定义本地数据库
/// </summary>
/// <param name="dataBaseName">本地数据库名</param>
/// <returns></returns>
public static string GetConection(string dataBaseName)
{
ConStr = "server=localhost;database=" + dataBaseName + ";integrated security=sspi;";

try
{
using (SqlConnection con = new SqlConnection(ConStr))
{
con.Open();
return "连接成功";
}
}
catch (SqlException e)
{
return "连接数据库出错"+e.Message;
}

}

/// <summary>
/// 静态方法方便调用
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (TransactionScope ts = new TransactionScope())//隐式事务
{
using (SqlConnection con = new SqlConnection(ConStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
try
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters); //添加参数数组

int i = cmd.ExecuteNonQuery();
ts.Complete();
//成功之后返回结果
return i;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
con.Close();//不管执行后是什么结果都关闭
}
}
}
}

//第二种事务方法,当然没有第一种好
//using (SqlConnection con = new SqlConnection(ConStr))
//{
// con.Open();
// SqlTransaction sqlTransaction = con.BeginTransaction();//在创建命令之前创建事物
// using (SqlCommand cmd = con.CreateCommand())
// {

// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters); //添加参数数组
// cmd.Transaction = sqlTransaction;
// try
// {
// int i = cmd.ExecuteNonQuery();
// sqlTransaction.Commit();//提交事务
// //成功之后返回结果
// return i;
// }
// catch (Exception ex)
// {
// sqlTransaction.Rollback();//回滚事务
// throw new Exception(ex.ToString());
// }
// finally
// {
// con.Close();//不管执行后是什么结果都关闭
// }
// }
//}
//比较不带事务的，当停电，关机等等原因
//不带事务，会把之前添加成功的会成功，加上事务，只要有失败就全部回滚
//using (SqlConnection con = new SqlConnection(ConStr))
//{
// con.Open();
// using (SqlCommand cmd = con.CreateCommand())
// {

// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters); //添加参数数组
// try
// {
// int i = cmd.ExecuteNonQuery();
// //成功之后返回结果
// return i;
// }
// catch (Exception ex)
// {
// throw new Exception(ex.ToString());
// }
// finally
// {
// con.Close();//不管执行后是什么结果都关闭
// }
// }
//}
}

public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);

//数据适配器
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);

return ds.Tables[0];
}
}
}

//DataBase to Program value
public static object ToDBValue(object str)//数据来自程序 给数据库
{
if (str==null)
{
return DBNull.Value;
}
else
{
return str;
}
}
public static object FromDBValue(object str)//数据来自数据库 给程序
{
if (str==DBNull.Value)
{
return null;
}
else
{
return str;
}
}
/// <summary>
/// 调用数据库连接池
/// </summary>
/// <param name="table"></param>
public static void sqlBulkCopy(DataTable table)
{
//数据库连接池
using (SqlBulkCopy sqlbc = new SqlBulkCopy(ConStr))
{
sqlbc.DestinationTableName = "T_QSUserAnswer";
sqlbc.ColumnMappings.Add("UserGuid", "T_UserGuid");
sqlbc.ColumnMappings.Add("InfoAnswer", "T_InfoAnswer");
sqlbc.ColumnMappings.Add("InfoScore", "T_InfoScore");
sqlbc.ColumnMappings.Add("ProTitGuid", "T_ProTitGuid");
sqlbc.WriteToServer(table);
}
}