C# SqlCommand.Add方法代码示例

 /// <summary>
 /// 执行指定数据库连接字符串的命令,返回指定数据库、表的索引信息
 /// </summary>
 /// <param name="connectionString">数据库连接字符串</param>
 /// <param name="database"></param>
 /// <param name="tableName"></param>
 /// <param name="schema"></param>
 /// <returns></returns>
 public static List<DbIndex> GetDbIndexs(string database, string tableName, string schema = "dbo", string connectionString = null)
 {
     #region SQL
     string sql = string.Format(@"
                                 select
                                 idx.name IndexName
                                 ,idx.type_desc IndexType
                                 ,idx.is_primary_key IsPrimaryKey
                                 ,idx.is_unique IsUnique
                                 ,idx.is_unique_constraint IsUniqueConstraint
                                 ,STUFF(
                                 (
                                     SELECT  ','+c.name from {0}.sys.index_columns ic
                                     inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
                                     WHERE ic.is_included_column = 0 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id
                                     ORDER BY ic.key_ordinal
                                     FOR XML PATH('')
                                 ),1,1,'') IndexColumns
                                 ,STUFF(
                                 (
                                     SELECT  ','+c.name from {0}.sys.index_columns ic
                                     inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
                                     WHERE ic.is_included_column = 1 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id
                                     ORDER BY ic.key_ordinal
                                     FOR XML PATH('')
                                 ),1,1,'') IncludeColumns
                                 from {0}.sys.indexes idx
                                 where object_id =OBJECT_ID(@tableName)", database);
     #endregion
     SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) };
     SqlParameterCollection paramCollection = new SqlCommand().Parameters;
     paramCollection.Add(param);
     DataTable dt = ExecuteDataTable(sql, connectionString, CommandType.Text, paramCollection);
     return dt.Rows.Cast<DataRow>().Select(row => new DbIndex()
     {
         IndexName = row.Field<string>("IndexName"),
         IndexType = row.Field<string>("IndexType"),
         IsPrimaryKey = row.Field<bool>("IsPrimaryKey"),
         IsUnique = row.Field<bool>("IsUnique"),
         IsUniqueConstraint = row.Field<bool>("IsUniqueConstraint"),
         IndexColumns = row.Field<string>("IndexColumns"),
         IncludeColumns = row.Field<string>("IncludeColumns")
     }).ToList();
 }

        /// <summary>
        /// Get Standard Levels By StandardList Data 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static DataTable GetStandardLevelbyStandardList(Thinkgate.Base.DataAccess.dtGeneric_Int ids,string StandardSet, int WorksheetId)
        {
            SqlParameterCollection parms = new SqlCommand().Parameters;
            SqlParameter idCollections = new SqlParameter("StandardIds", SqlDbType.Structured);
            idCollections.TypeName = "dbo.Generic_Int";
            idCollections.Value = ids;
            parms.Add(idCollections);
            parms.AddWithValue("StandardSet", StandardSet);
            parms.AddWithValue("WorksheetId", WorksheetId);

            DataTable dt = ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString, StoredProcedures.E3_GET_STANDARDLEVEL_BY_STANDARDLIST, CommandType.StoredProcedure, parms);
            return dt;
        }

 /// <summary>
 /// 执行指定数据库连接字符串的命令,返回指定数据库、表的字段信息
 /// </summary>
 /// <param name="connectionString">数据库连接字符串</param>
 /// <param name="database"></param>
 /// <param name="tableName"></param>
 /// <param name="schema"></param>
 /// <returns></returns>
 public static List<DbColumn> GetDbColumns(string database, string tableName, string schema = "dbo", string connectionString = null)
 {
     #region SQL
     string sql = string.Format(@"
                                 WITH indexCTE AS
                                 (
                                     SELECT
                                     ic.column_id,
                                     ic.index_column_id,
                                     ic.object_id
                                     FROM {0}.sys.indexes idx
                                     INNER JOIN {0}.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id
                                     WHERE  idx.object_id =OBJECT_ID(@tableName) AND idx.is_primary_key=1
                                 )
                                 select
                                 colm.column_id ColumnID,
                                 CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey,
                                 colm.name ColumnName,
                                 systype.name ColumnType,
                                 colm.is_identity IsIdentity,
                                 colm.is_nullable IsNullable,
                                 cast(colm.max_length as int) ByteLength,
                                 (
                                     case
                                         when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2
                                         when systype.name='nchar' and colm.max_length>0 then colm.max_length/2
                                         when systype.name='ntext' and colm.max_length>0 then colm.max_length/2
                                         else colm.max_length
                                     end
                                 ) CharLength,
                                 cast(colm.precision as int) Precision,
                                 cast(colm.scale as int) Scale,
                                 prop.value Remark
                                 from {0}.sys.columns colm
                                 inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
                                 left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
                                 LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id
                                 where colm.object_id=OBJECT_ID(@tableName)
                                 order by colm.column_id", database);
     #endregion
     SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) };
     SqlParameterCollection paramCollection = new SqlCommand().Parameters;
     paramCollection.Add(param);
     DataTable dt =  ExecuteDataTable(sql, connectionString, CommandType.Text, paramCollection);
     return dt.Rows.Cast<DataRow>().Select(row => new DbColumn()
     {
         ColumnID = row.Field<int>("ColumnID"),
         IsPrimaryKey = row.Field<bool>("IsPrimaryKey"),
         ColumnName = row.Field<string>("ColumnName"),
         ColumnType = row.Field<string>("ColumnType"),
         IsIdentity = row.Field<bool>("IsIdentity"),
         IsNullable = row.Field<bool>("IsNullable"),
         ByteLength = row.Field<int>("ByteLength"),
         CharLength = row.Field<int>("CharLength"),
         Scale = row.Field<int>("Scale"),
         Remark = row["Remark"].ToString()
     }).ToList();
 }

        protected void AddNewStaff(object sender, EventArgs e)
        {

            var environmentParametersViewModel = new EnvironmentParametersFactory(AppSettings.ConnectionStringName).GetEnvironmentParameters();
            var staffManagement = new StaffManagement(environmentParametersViewModel);

            var doesUserExist = staffManagement.DoesUserExist(loginID.Text);
            staffManagement.Dispose();

            if (doesUserExist)
            {
                var radalertscript = "<script language='javascript'>function f(){radalert('" + string.Format("Cannot Add User, this user already exists.  User: {0}", loginID.Text) + "', 300, 300, 'Duplicate User Detected'); Sys.Application.remove_load(f);}; Sys.Application.add_load(f);</script>";
                Page.ClientScript.RegisterStartupScript(GetType(), "radalert", radalertscript); 
                return;
            }

            var staffIdentificationTable = new dtGeneric_String_String();
            var schoolIdTable = new dtGeneric_Int();
            var userTypeIdTable = new dtGeneric_String();
            string restrictionsValue = string.Empty ;

            staffIdentificationTable.Rows.Add("FirstName", firstName.Text);
            staffIdentificationTable.Rows.Add("MiddleName", middleName.Text);
            staffIdentificationTable.Rows.Add("LastName", lastName.Text);
            staffIdentificationTable.Rows.Add("Email", email.Text);
            staffIdentificationTable.Rows.Add("LoginID", loginID.Text);
            
            switch (DataIntegrity.ConvertToInt(restrictionsDropdown.SelectedIndex))     // using index is not best way to accomplish this... but easy to fix in the future if we need to reorder dropdown
            {
                case (int)Restrictions.None:
                    staffIdentificationTable.Rows.Add("IsLockedOut", "false");
                    staffIdentificationTable.Rows.Add("IsApproved", "true");
                    staffIdentificationTable.Rows.Add("Restrictions", "None");
                    restrictionsValue = "None";
                    break;
                case (int)Restrictions.Revoked:
                    staffIdentificationTable.Rows.Add("IsLockedOut", "false");
                    staffIdentificationTable.Rows.Add("IsApproved", "false");
                    staffIdentificationTable.Rows.Add("Restrictions", "None");
                    restrictionsValue = "None";
                    break;
                case (int)Restrictions.LockedOut:
                    staffIdentificationTable.Rows.Add("IsLockedOut", "true");
                    staffIdentificationTable.Rows.Add("IsApproved", "true");
                    staffIdentificationTable.Rows.Add("Restrictions", "None");
                    restrictionsValue = "None";
                    break;
                case (int)Restrictions.ChangePassword:
                    staffIdentificationTable.Rows.Add("IsLockedOut", "false");
                    staffIdentificationTable.Rows.Add("IsApproved", "true");
                    staffIdentificationTable.Rows.Add("Restrictions", ThinkgateUser.ChangePasswordRestrictionValue);
                    restrictionsValue = ThinkgateUser.ChangePasswordRestrictionValue.ToString(CultureInfo.InvariantCulture);
                    break;
            }
            foreach (RadComboBoxItem item in schoolDropdown.Items)
            {
                var itemCheckbox = (CheckBox)item.FindControl("schoolCheckbox");
                var itemLabel = (Label)item.FindControl("schoolLabel");

                if (itemCheckbox != null && itemCheckbox.Checked && itemLabel.Text != @"All" && itemLabel.Text.ToLower().IndexOf("<img", StringComparison.Ordinal) == -1)
                {
                    schoolIdTable.Add(DataIntegrity.ConvertToInt(item.Value));
                }
            }
			StringBuilder userSyncRoles = new StringBuilder();
            foreach (RadComboBoxItem item in userTypeDropdown.Items)
            {
                var itemCheckbox = (CheckBox)item.FindControl("userTypeCheckbox");
                var itemLabel = (Label)item.FindControl("userTypeLabel");

                if (itemCheckbox != null && itemCheckbox.Checked && itemLabel.Text != @"All" && itemLabel.Text.ToLower().IndexOf("<img", StringComparison.Ordinal) == -1)
                {
                    userTypeIdTable.Add(item.Text);
					userSyncRoles.Append(itemLabel.Text);
                }
            }

            /*  Create the user record  */
            /* Validate Results - if error, give message and go back to user */
            SqlParameterCollection parms = new SqlCommand().Parameters;
            parms.AddWithValue("ApplicationName", AppSettings.ApplicationName);
            parms.AddWithValue("UserName", loginID.Text);
            parms.AddWithValue("FirstName", firstName.Text);
            parms.AddWithValue("MiddleName", middleName.Text);
            parms.AddWithValue("LastName", lastName.Text);
            parms.AddWithValue("Password", DistrictParms.LoadDistrictParms().DefaultPasswordEncrypted);
            parms.AddWithValue("PasswordSalt", DistrictParms.LoadDistrictParms().DefaultPasswordEncryptedSalt);
            parms.AddWithValue("Email", email.Text);
            parms.Add(ThinkgateDataAccess.GetParmFromTable(userTypeIdTable.ToSql(), "Roles"));
            parms.Add(ThinkgateDataAccess.GetParmFromTable(schoolIdTable.ToSql(), "Schools"));
            parms.AddWithValue("PrimarySchool", DataIntegrity.ConvertToInt(cmbPrimarySchool.SelectedItem.Value));
            parms.AddWithValue("PrimaryUser", cmbPrimaryUser.SelectedItem.Text);
            parms.AddWithValue("TeacherID", string.Empty);
            parms.AddWithValue("Restrictions", restrictionsValue);   

            var drNewStaffUserPage = ThinkgateDataAccess.FetchDataRow(AppSettings.ConnectionString,
                                                                      Base.Classes.Data.StoredProcedures.ASPNET_TG_SECURITY_USER_CREATE_USER,
                                                                      System.Data.CommandType.StoredProcedure,
                                                                      parms,
                                                                      SessionObject.GlobalInputs);
//.........这里部分代码省略.........