这里的linq to sql并未使用ef
主要有以下内容
1、新增
2、修改
3、删除
4、查询
1、新增,修改,删除获取sql语句通过DataContext.Log获取执行的sql语句
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
db.Log = sw;
db.SubmitChanges();
strSql = sb.ToString();
需要注意的是,通过Log获取sql语句的时候,只有在执行SubmitChanges后,才能拿到sql语句,并且拿到的语句也并不仅仅是只有操作语句,还有一个查询语句,如果不需要里面的查询,那么就需要自行处理下2、新增,修改,删除获取参数如下:
ChangeSet cs = db.GetChangeSet();
string insertValue = cs.Inserts.Count > 0 ? Newtonsoft.Json.JsonConvert.SerializeObject(cs.Inserts[0]) : "";
通过ChangeSet 对象中的Inserts Updates Deletes 可以获取到新增,修改,删除的集合,ChangeSet 获取内容,在SubmitChanges()执行之前3、当我们通过泛型去写的时候,我们可以通过 db.GetTable(typeof(T))类似的方式去执行linq,而不是使用db.Tables的方式,我们去看db.Tables的写法,实际就是返回db.GetTable(typeof(T))罢了,代码如下:
db.GetTable(typeof(T)).InsertOnSubmit(model);4、查询的时候,获取sql有点区别,我们需要通过IQueryable来实现,通过IEnumerable是无法实现的,获取sql的代码如下:
IQueryable<T> q = db.GetTable<T>().Where(where);//获取sql语句
System.Data.Common.DbCommand dc = db.GetCommand(q);
string strSql = dc.CommandText;
参数的获取如下:
string paras = "{0}: {1},";
foreach (DbParameter p in dc.Parameters)
{
strSql += string.Format(paras, p.ParameterName, p.Value);
}
ParameterName是参数名称,Value是参数的值
其中where中的条件类型为:Expression<Func<T, bool>>,此条件为参数时,我们只需要如下就可以使用:
public IQueryable<T> GetQueryable(Expression<Func<T, bool>> where){
IQueryable<T> q = db.GetTable<T>().Where(where);
}public List<实体> GetModels(){
return GetQueryable(m => m.Id = 20).ToList();
}
完整增加,删除,修改,查询的代码如下,至于实体要如何定义,却需要自行把握,我这里面,基类泛型,继承时,传实体的方式
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <param name="log"></param>
/// <returns></returns>
public int InsertModel(T model, string log)
{ try
{
string strSql = string.Empty; db.GetTable(typeof(T)).InsertOnSubmit(model); //字段与值得对应字符串
ChangeSet cs = db.GetChangeSet();
string insertValue = cs.Inserts.Count > 0 ? Newtonsoft.Json.JsonConvert.SerializeObject(cs.Inserts[0]) : ""; StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
db.Log = sw; db.SubmitChanges(); strSql = sb.ToString(); string strLog = string.Empty;
if (string.IsNullOrWhiteSpace(log))
{
strLog = "新增数据,SQL:" + strSql + ",字段值:" + insertValue;
}
else
{
strLog = log;
} InsertLog(LOGTYPE.新增.GetHashCode(), strLog); return model.Id;
}
catch (Exception ex)
{
this.WriteErrorLog(ex.Message);
throw ex;
} }/// <summary>
/// 更新
/// </summary>
/// <param name="log"></param>
private void Update(string log)
{
try
{
string strSql = string.Empty; //接收日志内容
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
db.Log = sw; //获取更改前后的值
ChangeSet cs = db.GetChangeSet(); string insertValue = cs.Inserts.Count > 0 ? Newtonsoft.Json.JsonConvert.SerializeObject(cs.Inserts[0]) : "";
string updateValue = cs.Updates.Count > 0 ? Newtonsoft.Json.JsonConvert.SerializeObject(cs.Updates[0]) : ""; db.SubmitChanges(); //获取sql语句,无法获取,官方说明是支持4.5以上的框架,并未使用4.5的框架测试
//通过 db.Log接收
strSql = sb.ToString();
string strLog = string.Empty;
if (string.IsNullOrWhiteSpace(log))
{
strLog = "修改数据,SQL:" + strSql + ",修改前字段值:" + insertValue + ",修改后的值:" + updateValue;
}
else
{
strLog = log;
} InsertLog(LOGTYPE.修改.GetHashCode(), strLog);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="log"></param>
/// <returns></returns>
public bool UpdateModel(string log)
{ try
{
Update(log);
return true;
}
catch (System.Data.Linq.ChangeConflictException ex)
{
foreach (System.Data.Linq.ObjectChangeConflict occ in db.ChangeConflicts)
{
//
occ.Resolve(System.Data.Linq.RefreshMode.KeepChanges);
}
Update(log);
//db.SubmitChanges();
return true;
}
catch (Exception ex)
{
WriteErrorLog(ex.Message);
return false; } }/// <summary>
/// 删除内容
/// </summary>
/// <param name="where"></param>
/// <param name="log"></param>
/// <returns></returns>
public bool Delete(Expression<Func<T, bool>> where,string log)
{
try
{
string strSql = string.Empty; db.GetTable(typeof(T)).DeleteOnSubmit(where); //字段与值得对应字符串
ChangeSet cs = db.GetChangeSet();
string deleteValue = cs.Deletes.Count > 0 ? Newtonsoft.Json.JsonConvert.SerializeObject(cs.Deletes) : ""; StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
db.Log = sw; db.SubmitChanges(); strSql = sb.ToString(); string strLog = string.Empty;
if (string.IsNullOrWhiteSpace(log))
{
strLog = "删除数据,SQL:" + strSql + ",字段值:" + deleteValue;
}
else
{
strLog = log;
} InsertLog(LOGTYPE.删除.GetHashCode(), strLog); return !string.IsNullOrWhiteSpace(deleteValue);
}
catch (Exception ex)
{
this.WriteErrorLog(ex.Message);
throw ex;
}
}/// <summary>
/// 返回IQueryable,调用如:GetQueryable(where,log).ToList()或者GetQueryable(where,log).First()
/// </summary>
/// <param name="where"></param>
/// <param name="log"></param>
/// <returns></returns>
public IQueryable<T> GetQueryable(Expression<Func<T, bool>> where, string log = "")
{
try
{
IQueryable<T> q = db.GetTable<T>().Where(where); //获取sql语句
System.Data.Common.DbCommand dc = db.GetCommand(q);
string strSql = dc.CommandText; string strLog = string.Empty;
if (string.IsNullOrWhiteSpace(log))
{
string paras = "{0}: {1},";
strSql = "linq获取数据,SQL:" + strSql + ",参数:";
foreach (DbParameter p in dc.Parameters)
{
strSql += string.Format(paras, p.ParameterName, p.Value);
}
}
else
{
strLog = log;
} InsertLog(LOGTYPE.查询.GetHashCode(), strLog); return q;
}
catch (Exception ex)
{
WriteErrorLog(ex.Message);
throw new Exception("执行失败!失败原因:" + ex.Message);
}
}