//执行DataTable查询
public int ExecuteSql(string SQLString)
{
context = new db_SaberEntities();
using (SqlConnection connection = (SqlConnection)context.Database.Connection)
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
cmd.Dispose();
connection.Close();
connection.Dispose();
return(rows);
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
connection.Dispose();
throw e;
}
}
}
}
//基于SqlBulkCopy进行批量添加(表结构相同)
public void DataTableToSQLServer(DataTable dt, string TableName)
{
context = new db_SaberEntities();
using (SqlConnection destinationConnection = (SqlConnection)context.Database.Connection)
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
bulkCopy.DestinationTableName = TableName;//要插入的表的表明
foreach (DataColumn Columninfo in dt.Columns)
{
bulkCopy.ColumnMappings.Add(Columninfo.ColumnName, Columninfo.ColumnName);//映射字段名 DataTable列名 ,数据库 对应的列名
}
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(ArrayList SQLStringList)
{
context = new db_SaberEntities();// EFContextFactory.GetCurrentDbContext();
using (SqlConnection connection = (SqlConnection)context.Database.Connection)
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (Exception E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 帮我们返回当前线程内的数据库上下文，如果当前线程内没有上下文，那么创建一个上下文，并保证
/// 上下文是实例在线程内部唯一
/// 在EF4.0以前使用ObjectsContext对象
/// </summary>
/// <returns></returns>
public static db_SaberEntities GetCurrentDbContext()
{
//当第二次执行的时候直接取出线程嘈里面的对象
//CallContext:是线程内部唯一的独用的数据槽(一块内存空间)
//数据存储在线程栈中
//线程内共享一个单例
db_SaberEntities dbcontext = CallContext.GetData("DbContext") as db_SaberEntities;
//判断线程里面是否有数据
if (dbcontext == null) //线程的数据槽里面没有次上下文
{
dbcontext = new db_SaberEntities(); //创建了一个EF上下文
//存储指定对象
CallContext.SetData("DbContext", dbcontext);
}
return(dbcontext);
}
//执行DataTable查询
public DataSet Query(string SQLString)
{
context = new db_SaberEntities();
using (SqlConnection connection = (SqlConnection)context.Database.Connection)
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return(ds);
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public object GetSingle(string SQLString)
{
context = new db_SaberEntities();
using (SqlConnection connection = (SqlConnection)context.Database.Connection)
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmd.Dispose();
connection.Close();
connection.Dispose();
return(null);
}
else
{
cmd.Dispose();
connection.Close();
connection.Dispose();
return(obj);
}
}
catch (System.Data.SqlClient.SqlException e)
{
cmd.Dispose();
connection.Close();
connection.Dispose();
throw e;
}
}
}
}
//测试用
public RepositoryBase()
{
this.context = EFContextFactory.GetCurrentDbContext();
}
//提供IOC注入方式接口
public RepositoryBase(db_SaberEntities context)
{
this.context = context;
}
