public static void InitTableForSqlServer(TableEntity table) //初始化增删改等语句sqlserver专用
{
string Fields = CommonUtil.GetFieldsStr(table.AllFieldList, "[", "]");
string FieldsAt = CommonUtil.GetFieldsAtStr(table.AllFieldList);
string FieldsEq = CommonUtil.GetFieldsEqStr(table.AllFieldList, "[", "]");
string FieldsExtKey = CommonUtil.GetFieldsStr(table.ExceptKeyFieldList, "[", "]");
string FieldsAtExtKey = CommonUtil.GetFieldsAtStr(table.ExceptKeyFieldList);
string FieldsEqExtKey = CommonUtil.GetFieldsEqStr(table.ExceptKeyFieldList, "[", "]");
table.AllFields = Fields;
table.AllFieldsAt = FieldsAt;
table.AllFieldsAtEq = FieldsEq;
table.AllFieldsExceptKey = FieldsExtKey;
table.AllFieldsAtExceptKey = FieldsAtExtKey;
table.AllFieldsAtEqExceptKey = FieldsEqExtKey;
table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, Fields, FieldsAt);
if (!string.IsNullOrEmpty(table.KeyName)) //有主键(table has primary key)
{
if (table.IsIdentity) //是自增 (and the parmary is identity)
{
table.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", table.TableName, FieldsExtKey, FieldsAtExtKey);
table.InsertReturnIdSql = string.Format("INSERT INTO [{0}]({1})VALUES({2});SELECT @@IDENTITY;", table.TableName, FieldsExtKey, FieldsAtExtKey);
}
table.InsertIdentitySql = string.Format("SET IDENTITY_INSERT [{0}] ON;INSERT INTO [{0}]({1})VALUES({2});SET IDENTITY_INSERT [{0}] OFF", table.TableName, Fields, FieldsAt);
table.DeleteByIdSql = string.Format("DELETE FROM [{0}] WHERE [{1}]=@id", table.TableName, table.KeyName);
table.DeleteByIdsSql = string.Format("DELETE FROM [{0}] WHERE [{1}] IN @ids", table.TableName, table.KeyName);
table.GetByIdSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", Fields, table.TableName, table.KeyName);
table.GetByIdsSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", Fields, table.TableName, table.KeyName);
table.UpdateSql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", table.TableName, FieldsEqExtKey, table.KeyName);
}
table.DeleteAllSql = string.Format("DELETE FROM [{0}] ", table.TableName);
table.GetAllSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) ", Fields, table.TableName);
}
