在C#中的SQL 传入like参数和时间类型

今天修改个DB upgrade version verify tool. 在写sql 语句的时候遇到一些问题，查了些资料才知道该如何写

1. 在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;
            }
         }

View Code

　　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;
            }
        }

View Code

 

 

转载于:https://www.cnblogs.com/binyao/archive/2013/05/20/3088875.html