自增id数据表插入后获取新插入记录的Id
insert into table(field1, field2) values('value1', 'value2');select @@IDENTITY
重置自增ID
删除表中所有数据: truncate table TableName
不删除表的数据(or有外键的情况下): DBCC CHECKIDENT('TableName', RESEED, 0)
检测SQL语句语法错误
SET PARSEONLY 为 ON 时,SQL Server 只分析语句。
SET PARSEONLY 为 OFF 时,SQL Server 编译并执行语句。
SET NOEXEC 为 ON 时,SQL Server 将编译每一条Tran-SQL语句但并不执行它们。
SET NOEXEC 为 OFF 时,SQL Server 编译并执行语句。
public virtual int ValidateSQL(string sql)
{
//SQL语句只分析语句
int bResult = 0;
DbCommand cmd = CreateCommand();
using (DbConnection conn = CreateConnection())
{
conn.ConnectionString = connectionString;
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SET PARSEONLY ON";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
bResult = 1;
}
catch (Exception ex)
{
//LogHelper.Error("SQL语法错误" + ex);
bResult = 0;
}
finally
{
cmd.CommandText = "SET PARSEONLY OFF";
cmd.ExecuteNonQuery();
}
return bResult;
}
}
like 转义通配符[]
select * from test where date like '%\[1990\]%" escape '\'
(匹配'[1990]')