C# SqlCommandBuilder.QuoteIdentifier方法代码示例

 public static string QuoteSchemaAndTable(this string sqlQuery, string schemaName, string tableName)
 {
     var cb = new SqlCommandBuilder();
     return string.Format(sqlQuery, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(tableName));
 }

		public void DefaultProperties ()
		{
			SqlCommandBuilder cb = new SqlCommandBuilder ();
			Assert.AreEqual ("[", cb.QuotePrefix, "#5");
			Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
			Assert.AreEqual (".", cb.CatalogSeparator, "#2");
			//Assert.AreEqual ("", cb.DecimalSeparator, "#3");
			Assert.AreEqual (".", cb.SchemaSeparator, "#4");
			Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
			Assert.AreEqual ("[monotest]", cb.QuoteIdentifier ("monotest"), "#7");
			Assert.AreEqual ("\"monotest\"", cb.UnquoteIdentifier ("\"monotest\""), "#8");
			//Assert.AreEqual (cb.ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
			// FIXME: test SetAllValues
		}

    private SqlCommand BuildUpdateCommand(string tableName, DataTable attributes)
    {
        if (attributes == null || attributes.Rows.Count <= 0) {
            return null;
        }
        DataRow row = attributes.Rows[0];
        DataTable tableSchema = this.GetSchema(tableName);
        /*
        using(var writer = new System.IO.StringWriter()) {
            tableSchema.WriteXml(writer);
            Console.WriteLine(writer.ToString());
        }
        */
        attributes.Columns["Part_num"].ColumnName = "Part Number";

        SqlCommand cmd = new SqlCommand();
        SqlCommandBuilder bld = new SqlCommandBuilder();
        int modifiedColumns = 0;
        int whereColumns = 0;
        string tableNameEscaped = bld.QuoteIdentifier(tableName);
        string sqlBody = "";
        string sqlWhere = "WHERE";
        sqlBody = string.Format("UPDATE {0} SET", tableNameEscaped);
        foreach (DataRow schemaColumn in tableSchema.Rows) {
            string columnName = (string)schemaColumn["COLUMN_NAME"];
            //TODO: Update DB schema to have proper primary keys, and figure out how to detect them ;-)
            string columnKey = (columnName == "Part Number" ? "PRI" : "");
            string dataType = (string)schemaColumn["DATA_TYPE"];
            string columnNameEscaped = bld.QuoteIdentifier(columnName);
            if (columnKey.Length > 0) {
                if (columnKey == "PRI") {
                    // Add WHERE clause to satisfy PRImary key
                    if (attributes.Columns.Contains(columnName)) {
                        object originalValue = row[columnName, DataRowVersion.Original];
                        string paramName = "@w" + modifiedColumns.ToString();
                        sqlWhere += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (whereColumns++ > 0 ? " AND " : " "));
                        cmd.Parameters.AddWithValue(paramName, originalValue);
                    }
                }
                // Never update any UNIque, PRImary or MULty-key columns
                continue;
            }
            if (Array.IndexOf(stringTypes, dataType.ToUpper()) < 0) {
                // For now, ignore non-string parameters
                continue;
            }
            if (attributes.Columns.Contains(columnName)) {
                object orignalObject = row[columnName, DataRowVersion.Original];
                object currentObject = row[columnName, DataRowVersion.Current];
                string originalValue = Convert.IsDBNull(orignalObject) ? "" : (string)orignalObject;
                string currentValue = Convert.IsDBNull(currentObject) ? "" : (string)currentObject;
                if (originalValue != currentValue) {
                    // Add SET expression
                    string paramName = "@p" + modifiedColumns.ToString();
                    sqlBody += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (modifiedColumns++ > 0 ? ", " : " "));
                    cmd.Parameters.AddWithValue(paramName, currentValue);
                }
            }
        }
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = string.Format("{0} {1}", sqlBody, sqlWhere);
        if (modifiedColumns > 0) {
            return cmd;
        } else {
            return null;
        }
    }

 public override void NewPart(PartType partType, ErrorHandler errorHandler)
 {
     //TODO: See note in PartsDb.NewPart()
     SqlCommandBuilder bld = new SqlCommandBuilder();
     SqlCommand cmd = new SqlCommand();
     string tableNameEscaped = bld.QuoteIdentifier(partType.name);
     string partNumColumnEscaped = bld.QuoteIdentifier("Part Number");
     string partTypeColumnEscaped = bld.QuoteIdentifier("Part Type");
     cmd.CommandType = System.Data.CommandType.Text;
     cmd.CommandText = string.Format("SELECT TOP 1 {0} FROM {1} ORDER BY {0} DESC", partNumColumnEscaped, tableNameEscaped);
     Execute(cmd, delegate(DataTable partNumResult) {
         if (partNumResult.Rows.Count > 0) {
             DataRow row = partNumResult.Rows[0];
             string Part_num = Util.PartNumberString(Util.PartNumberInteger((string)row["Part Number"]) + 1);
     #if DEBUG
             Console.WriteLine("New part num: {0}", Part_num);
     #endif
             SqlCommand insertCmd = new SqlCommand();
             insertCmd.CommandType = System.Data.CommandType.Text;
             insertCmd.CommandText = string.Format("INSERT INTO {0} ({1}, {2}) VALUES (@partNum, @partType);", tableNameEscaped, partNumColumnEscaped, partTypeColumnEscaped);
             insertCmd.Parameters.AddWithValue("@partNum", Part_num);
             insertCmd.Parameters.AddWithValue("@partType", partType.name);
             Execute(insertCmd, null, errorHandler);
         }
     }, errorHandler);
 }

 public void RepairMissingAttributes()
 {
     this.GetPartTypes(delegate(List<PartType> partTypes) {
         foreach (PartType partType in partTypes) {
             SqlCommand cmd = new SqlCommand();
             SqlCommandBuilder bld = new SqlCommandBuilder();
             Connect();
             cmd.Connection = _con;
             string typeAttributesTable = string.Format("{0}_attributes", partType.name);
             string partNumColumnEscaped = bld.QuoteIdentifier("Part_num");
             string partsTableEscaped = bld.QuoteIdentifier("Parts");
             string partTypesTableEscaped = bld.QuoteIdentifier("Part_types");
             string partTypeIdColumnEscaped = bld.QuoteIdentifier("Part_type_id");
             string typeAttributesTableEscaped = bld.QuoteIdentifier(typeAttributesTable);
             cmd.CommandText = string.Format("SELECT {0} FROM {1} AS P NATURAL JOIN {2} WHERE P.{3} = @partType AND P.{0} NOT IN ( SELECT {0} FROM {4} )",
                 partNumColumnEscaped,
                 partsTableEscaped,
                 partTypesTableEscaped,
                 partTypeIdColumnEscaped,
                 typeAttributesTableEscaped);
             cmd.Parameters.AddWithValue("@partType", partType.typeId);
     #if DEBUG
             Console.WriteLine(Util.SqlCommandToString(cmd));
     #endif
             SqlDataReader typeReader = cmd.ExecuteReader();
             List<SqlCommand> insertCommands = new List<SqlCommand>();
             try {
                 while (typeReader.Read()) {
                     string partNum = (string)typeReader["Part_num"];
                     SqlCommand insertCmd = new SqlCommand();
                     insertCmd.Connection = _con;
                     insertCmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES (@partNum)",
                         typeAttributesTableEscaped,
                         partNumColumnEscaped);
                     insertCmd.Parameters.AddWithValue("@partNum", partNum);
                     insertCommands.Add(insertCmd);
                 }
             } finally {
                 typeReader.Close();
             }
             foreach (SqlCommand insertCmd in insertCommands) {
                 int rowsAffected = insertCmd.ExecuteNonQuery();
                 if (rowsAffected < 1) {
                     Console.WriteLine("Warning: Failed to add {0} to {1} table",
                             (string)insertCmd.Parameters["@partNum"].Value,
                             typeAttributesTable);
                 }
             }
         }
     }, null);
 }

 public override void GetPart(string Part_num, PartType partType, PartHandler handler, ErrorHandler errorHandler)
 {
     SqlCommandBuilder bld = new SqlCommandBuilder();
     SqlCommand cmd = new SqlCommand();
     string tableNameEscaped = bld.QuoteIdentifier(partType.name);
     string partNumColumnEscaped = bld.QuoteIdentifier("Part Number");
     cmd.CommandType = System.Data.CommandType.Text;
     cmd.CommandText = string.Format("SELECT * FROM {0} WHERE {1} = @partNum", tableNameEscaped, partNumColumnEscaped);
     cmd.Parameters.AddWithValue("@partNum", Part_num);
     Execute(cmd, delegate(DataTable partResult) {
         Part part = null;
         if (partResult.Rows.Count > 0) {
             partResult.Columns["Part Number"].ColumnName = "Part_num";
             part = new Part(Part_num, partType.typeId, partType.name, partResult);
         }
         if (handler != null) {
             handler(part);
         }
     }, errorHandler);
 }

 public override void GetParts(PartType partType, PartsHandler partsHandler, ErrorHandler errorHandler)
 {
     SqlCommandBuilder bld = new SqlCommandBuilder();
     SqlCommand cmd = new SqlCommand();
     string tableNameEscaped = bld.QuoteIdentifier(partType.name);
     cmd.CommandType = System.Data.CommandType.Text;
     #if DEBUG
     cmd.CommandText = string.Format("SELECT TOP 4 * FROM {0} ORDER BY [Part Number] ASC", tableNameEscaped);
     #else
     cmd.CommandText = string.Format("SELECT * FROM {0} ORDER BY [Part Number] ASC", tableNameEscaped);
     #endif
     Execute(cmd, delegate(DataTable result) {
         result.Columns["Part Number"].ColumnName = "Part_num";
         //result.Columns.Remove("Part Type");
         PartCollection parts = new PartCollection(result);
         if (partsHandler != null) {
             partsHandler(parts);
         }
     }, errorHandler);
 }

        public SqlId(string value)
        {
            var sb = new StringBuilder();

            var cmdBuilder = new SqlCommandBuilder();

            value.Split('.').ToList().ForEach(segment =>
            {
                sb.Append(cmdBuilder.QuoteIdentifier(segment));
                sb.Append('.');
            });

            if (sb[sb.Length - 1] == '.')
            {
                sb.Remove(sb.Length - 1, 1);
            }

            this.Value = sb.ToString();
        }

		[Test] // QuoteIdentifier (String)
		public void QuoteIdentifier_UnquotedIdentifier_Null ()
		{
			SqlCommandBuilder cb = new SqlCommandBuilder ();
			try {
				cb.QuoteIdentifier ((string) null);
				Assert.Fail ("#1");
			} catch (ArgumentNullException ex) {
				Assert.AreEqual (typeof (ArgumentNullException), ex.GetType (), "#2");
				Assert.IsNull (ex.InnerException, "#3");
				Assert.IsNotNull (ex.Message, "#4");
				Assert.AreEqual ("unquotedIdentifier", ex.ParamName, "#5");
			}
		}

		public void QuoteIdentifier_PrefixSuffix_NoMatch ()
		{
			SqlCommandBuilder cb;
		
			cb = new SqlCommandBuilder ();
			cb.QuoteSuffix = "\"";
			try {
				cb.QuoteIdentifier ("mono");
				Assert.Fail ("#A1");
			} catch (ArgumentException ex) {
				// Specified QuotePrefix and QuoteSuffix values
				// do not match
				Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
				Assert.IsNull (ex.InnerException, "#A3");
				Assert.IsNotNull (ex.Message, "#A4");
				Assert.IsTrue (ex.Message.IndexOf ("QuotePrefix") != -1, "#A5:" + ex.Message);
				Assert.IsTrue (ex.Message.IndexOf ("QuoteSuffix") != -1, "#A6:" + ex.Message);
				Assert.IsNull (ex.ParamName, "#A7");
			}

			cb = new SqlCommandBuilder ();
			cb.QuotePrefix = "\"";
			try {
				cb.QuoteIdentifier ("mono");
				Assert.Fail ("#B1");
			} catch (ArgumentException ex) {
				// Specified QuotePrefix and QuoteSuffix values
				// do not match
				Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#B2");
				Assert.IsNull (ex.InnerException, "#B3");
				Assert.IsNotNull (ex.Message, "#B4");
				Assert.IsTrue (ex.Message.IndexOf ("QuotePrefix") != -1, "#B5:" + ex.Message);
				Assert.IsTrue (ex.Message.IndexOf ("QuoteSuffix") != -1, "#B6:" + ex.Message);
				Assert.IsNull (ex.ParamName, "#B7");
			}
		}

        private static string InitMetadataSql(string metadataTable, string schemaName)
        {
            if (string.IsNullOrEmpty(metadataTable)) throw new ArgumentNullException("metadataTable", "Akka.Persistence.SqlServer metadata table name is required");
            schemaName = schemaName ?? "dbo";

            var cb = new SqlCommandBuilder();
            return string.Format(SqlMetadataFormat, cb.QuoteIdentifier(schemaName), cb.QuoteIdentifier(metadataTable), cb.UnquoteIdentifier(schemaName), cb.UnquoteIdentifier(metadataTable));

        }