C# SqlCommand.CreateParameter方法代码示例

        public void Test_WithOutputEnumParameter_ShouldReturnEnum()
        {
            using (var conn = new SqlConnection(_connString))
            {
                conn.Open();
                var cmd = new SqlCommand("set @output = @input", conn);
                cmd.Parameters.AddWithValue("@input", MyEnum.B);

                var outputParam = cmd.CreateParameter();
                outputParam.ParameterName = "@output";
                outputParam.DbType = DbType.Int32;
                outputParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outputParam);

                cmd.ExecuteNonQuery();

                Assert.Equal((MyEnum)outputParam.Value, MyEnum.B);
            }
        }

        private void InsertRowInternal(SqlConnection con, ref SqlCommand cmd, ref SqlParameter[] parameters, string tableName, int row)
        {
            // cannot use DataTable: it does not handle well char[] values and variant and also does not support sparse/column set ones
            StringBuilder columnsText = new StringBuilder();
            StringBuilder valuesText = new StringBuilder();

            // create the command and parameters on first call, reuse afterwards (to reduces table creation overhead)
            if (cmd == null)
            {
                cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                // need to unbind existing parameters and re-add the next set of values
                cmd.Parameters.Clear();
            }

            if (parameters == null)
            {
                parameters = new SqlParameter[_columns.Length];
            }

            object[] rowValues = _rows[row];

            // there is a limit of parameters to be sent (2010)
            for (int ci = 0; ci < _columns.Length; ci++)
            {
                if (cmd.Parameters.Count >= MaxParameterCount)
                {
                    // reached the limit of max parameters, cannot continue
                    // theoretically, we could do INSERT + UPDATE. practically, chances for this to happen are almost none since nulls are skipped
                    rowValues[ci] = DBNull.Value;
                    continue;
                }

                if (SkipOnInsert(ci))
                {
                    // cannot insert timestamp
                    // insert of values into columnset columns are also not supported (use sparse columns themselves)
                    continue;
                }

                bool isNull = (rowValues[ci] == DBNull.Value || rowValues[ci] == null);

                if (isNull)
                {
                    // columns such as sparse cannot have DEFAULT constraint, thus it is safe to ignore the value of the column when inserting new row
                    // this also significantly reduces number of columns updated during insert, to prevent "The number of target 
                    // columns that are specified in an INSERT, UPDATE, or MERGE statement exceeds the maximum of 4096."
                    continue;
                }

                SqlParameter p = parameters[ci];

                // construct column list
                if (columnsText.Length > 0)
                {
                    columnsText.Append(", ");
                    valuesText.Append(", ");
                }

                columnsText.AppendFormat("[{0}]", _columnNames[ci]);

                if (p == null)
                {
                    p = cmd.CreateParameter();
                    p.ParameterName = "@p" + ci;
                    p.SqlDbType = _columns[ci].Type;

                    parameters[ci] = p;
                }

                p.Value = rowValues[ci] ?? DBNull.Value;

                cmd.Parameters.Add(p);

                valuesText.Append(p.ParameterName);
            }

            Debug.Assert(columnsText.Length > 0, "Table that have only TIMESTAMP, ColumnSet or Sparse columns are not allowed - use primary key in this case");

            cmd.CommandText = string.Format("INSERT INTO {0} ( {1} ) VALUES ( {2} )", tableName, columnsText, valuesText);

            cmd.ExecuteNonQuery();
        }