在C#中的SQL 传入like参数和时间类型
今天修改个DB upgrade version verify tool. 在写sql 语句的时候遇到一些问题,查了些资料才知道该如何写
- 在SQL中如何使用like: 加N会将这个key声明为Unicode,否则为默认值
It's declaring the string as nvarchar
data type, rather than varchar
You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.
private const string CheckDatabaseFullName = "Select name from sys.databases where name like N'{0}%' order by create_date"; //传入需要like的参数 public string GetBackupedPartitionMasterDatabase(string database) { using (SqlConnection conn = new SqlConnection(MasterConnectionString)) { conn.Open(); string name = null; using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(CheckDatabaseFullName, database); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { name = reader["name"].ToString(); Logger.Info(string.Format("Database: {0} exists", name)); } } } return name; } }
2. C#中SQL语句时间比大小
private const string CheckDatabaseFullName = "Select name from sys.databases where create_date>@datetime and name like N'PartitionMaster_Backup%' order by create_date"; public string GetBackupedPartitionMasterDatabase() { using (SqlConnection conn = new SqlConnection(MasterConnectionString)) { //DateTime dt = new DateTime(2012, 10, 11); DateTime dt = DateTime.Now().ToUniversalTime(); conn.Open(); string name = null; using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = CheckDatabaseFullName; cmd.Parameters.Add("@datetime", SqlDbType.DateTime); cmd.Parameters[0].Value = dt; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { name = reader["name"].ToString(); Logger.Info(string.Format("Database: {0} exists", name)); } } } return name; } }