public
static
class
StaticClass
{
///
<summary>
///
批量删除
///
</summary>
///
<typeparam name="T"></typeparam>
///
<param name="table"></param>
///
<param name="predicate"></param>
///
<returns></returns>
public
static
int
Delete<T>(
this
Table<T> table, Expression<Func<T,
bool
>> predicate)
where
T :
class
{
string
tableName = table.Context.Mapping.GetTable(
typeof
(T)).TableName;
DbCommand command=table.Context.GetCommand(table.Where(predicate));
string
sqlCondition = command.CommandText;
sqlCondition = sqlCondition.Substring(sqlCondition.LastIndexOf(
"
WHERE
"
, StringComparison.InvariantCultureIgnoreCase) +
6
);
//
去掉表中出现的 t0
sqlCondition = sqlCondition.Replace(
"
[t0].
"
,
""
);
string
commandText =
string
.Format(
"
Delete FROM {0} WHERE {1}
"
, tableName , sqlCondition);
command.CommandText = commandText;
//
执行
try
{
if
(command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
return
command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
command.Dispose();
}
}
///
<summary>
///
批量更新
///
</summary>
///
<typeparam name="T"></typeparam>
///
<param name="table"></param>
///
<param name="predicate">
查询条件表达式
</param>
///
<param name="updater">
更新表达式
</param>
///
<returns>
影响的行数
</returns>
public
static
int
Update<T>(
this
Table<T> table, Expression<Func<T,
bool
>> predicate, Expression<Func<T, T>> updateExp)
where
T :
class
{
string
tableName = table.Context.Mapping.GetTable(
typeof
(T)).TableName;
DbCommand command = table.Context.GetCommand(table.Where(predicate));
string
sqlCondition = command.CommandText;
sqlCondition = sqlCondition.Substring(sqlCondition.LastIndexOf(
"
WHERE
"
, StringComparison.InvariantCultureIgnoreCase) +
6
);
//
获取Update的赋值语句
var
updateMemberExp = (MemberInitExpression)updateExp.Body;
var
updateMemberCollection = updateMemberExp.Bindings.Cast<MemberAssignment>().Select(c =>
{
var
p = command.CreateParameter();
p.ParameterName = c.Member.Name;
p.Value = ((ConstantExpression)c.Expression).Value;
return
p;
}).ToArray();
string
sqlUpdateBlock =
string
.Join(
"
,
"
, updateMemberCollection.Select(c =>
string
.Format(
"
[{0}]=@{0}
"
, c.ParameterName)).ToArray());
//
组合SQL 语句
string
commandText =
string
.Format(
"
UPDATE {0} SET {1} FROM {0} AS t0 WHERE {2}
"
, tableName, sqlUpdateBlock, sqlCondition);
//
获取参数数组
command.Parameters.AddRange(updateMemberCollection);
command.CommandText = commandText;
//
执行
try
{
if
(command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
return
command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
command.Dispose();
}
}
}
// 查询
DataClasses1DataContext dbo = new DataClasses1DataContext();
Table<K_Menu> tb = dbo.GetTable<K_Menu>();
List<K_MenuCopy> list;//= new List<K_Menu>();
using (DbConnection dbCon = tb.Context.Connection)
{
var query1 = tb.Select(a => new { a.BigImage, a.Image, a.MenuID });
DbCommand dbCommand = tb.Context.GetCommand(query1);
dbCon.Open();
using (DbDataReader dr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
list = dbo.Translate<K_MenuCopy>(dr).ToList();
}
}
