C# SqlConnection.CreateCommand方法代码示例

        public static DataTable GetHandoverLogByHandoverLogId(int handoverLogId)
        {
            string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT  WorkingTeamShiftLog.*,
                                                StaffInfo.Name AS ChargeManName, ProductLine.Name AS ProductLineName,
                                                WorkingTeam.Name AS WorkingTeamName
                                        FROM    WorkingTeam INNER JOIN
                                                StaffInfo INNER JOIN
                                                WorkingTeamShiftLog ON StaffInfo.ID = WorkingTeamShiftLog.ChargeManID INNER JOIN
                                                ProductLine ON WorkingTeamShiftLog.ProductLineID = ProductLine.ID ON
                                                WorkingTeam.ID = WorkingTeamShiftLog.WorkingTeamID
                                        WHERE   (WorkingTeamShiftLog.ID = @workingTeamShiftLogID)";

                command.Parameters.Add(new SqlParameter("workingTeamShiftLogID", handoverLogId));
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
            }

            return ds.Tables[0];
        }

        public void PersistUpdateOf(IAggregateRoot entity)
        {
            Account account = entity as Account;

            string sql = "UPDATE Account SET [email protected] WHERE [email protected]";
            SqlParameter parameter1 = new SqlParameter("@Id", account.Id);
            SqlParameter parameter2= new SqlParameter("@Balance", account.Balance);

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(parameter1);
                    cmd.Parameters.Add(parameter2);

                    int affectedCount = cmd.ExecuteNonQuery();

                    if (affectedCount <= 0) 
                    {
                        throw new Exception("update Account failed");
                    }
                }
            }
        }

        // This function will be used to execute CUD(CRUD) operation of parameterized commands
        internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
        {
            int result = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
            return (result > 0);
        }

        // This function will be used to execute R(CRUD) operation of parameterized commands
        internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
        {
            DataTable table = new DataTable();

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(param);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return table;
        }

 public bool CreateUser(UserInfo userInfo)
 {
     var salt = _helper.GenerateSalt();
     var pas = _helper.EncodePassword(userInfo.Password, salt);
     using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
     {
         using (var command = connection.CreateCommand())
         {
             command.CommandType = CommandType.StoredProcedure;
             command.CommandText = "sp_CreateUser";
             command.Parameters.AddWithValue("@Login", userInfo.Login).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Password", pas).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@PasswordSalt", salt).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Email", userInfo.Email).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Fio", userInfo.Fio).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Address", userInfo.Address).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Phone", userInfo.Phone).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Mobile", userInfo.Mobile).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Country", userInfo.Country).SqlDbType = SqlDbType.Char;
             command.Parameters.AddWithValue("@Zip", userInfo.Zip).SqlDbType = SqlDbType.NVarChar;
             var retParam = command.Parameters.AddWithValue("@Return", SqlDbType.Int);
             retParam.Direction = ParameterDirection.ReturnValue;
             connection.Open();
             command.ExecuteNonQuery();
             return (int)retParam.Value == 1;
         }
     }
 }

        public IEnumerable<UserInfoShort> EnumerateClients(string fio, string login, int pageNumber, int countPerPage)
        {
            using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "sp_EnumerateClients";
                    command.Parameters.AddWithValue("@FIO", fio ?? (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@Login",
                        login != null ? login.ToLower() : (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@PageNumber", pageNumber).SqlDbType = SqlDbType.Int;
                    command.Parameters.AddWithValue("@CountPerPage", countPerPage).SqlDbType = SqlDbType.Int;
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        var lst = new List<UserInfoShort>();
                        while (reader.Read())
                        {
                            lst.Add(new UserInfoShort
                                {
                                    UserId = (Guid)reader["UserId"],
                                    CounOfCardAccounts = (int)reader["CounOfCardAccounts"],
                                    FIO = (string)reader["FIO"],
                                    Login = (string)reader["Login"],
                                    RegistrationDate = (DateTime)reader["RegistrationDate"]
                                });
                        }

                        return lst;
                    }
                }
            }
        }

        public void FixtureTearDown()
        {
            // Delete database
            SqlConnection cn = new SqlConnection(ConnString);
            cn.Open();

            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cmd = cn.CreateCommand();
            cmd.CommandText = "drop database [ScrewTurnWikiTest]";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cn.Close();
        }

        public void FixtureTearDown()
        {
            // Delete database
            SqlConnection cn = new SqlConnection(ConnString);
            cn.Open();

            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cmd = cn.CreateCommand();
            cmd.CommandText = "drop database [ScrewTurnWikiTest]";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cn.Close();

            // This is neede because the pooled connection are using a session
            // that is now invalid due to the commands executed above
            SqlConnection.ClearAllPools();
        }

        public Response DB(dynamic parameters)
        {
            ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["Default"];

            var sb = new StringBuilder("<html><body><pre>");
            try
            {
                if (null != css && false == String.IsNullOrWhiteSpace(css.ConnectionString))
                {
                    using (var conn = new SqlConnection(css.ConnectionString))
                    {
                        conn.Open();
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT GETDATE()";
                            sb.AppendLine(cmd.ExecuteScalar().ToString());
                        }
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT @@VERSION";
                            sb.AppendLine(cmd.ExecuteScalar().ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                sb.Append(ex.Message);
            }
            sb.Append("</pre></body></html>");
            return sb.ToString();
        }

 protected void Associate_Authenticate(object sender, AuthenticateEventArgs e)
 {
     SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssociateConn"].ConnectionString);
     SqlCommand cmd = sqlConn.CreateCommand();
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "dbo.Get_Login";
     cmd.Parameters.AddWithValue("@id", AssociateLogin.UserName);
     cmd.Parameters.AddWithValue("@password", AssociateLogin.Password);
     if (sqlConn.State != ConnectionState.Open)
         sqlConn.Open();
     object o = cmd.ExecuteScalar();
     int Authenticated = Convert.ToInt32(cmd.ExecuteScalar());
     if (Authenticated == 1)
     {
         HttpContext.Current.Session["UserName"] = AssociateLogin.UserName.ToString();
         SqlCommand cmdRole = sqlConn.CreateCommand();
         cmdRole.CommandType = System.Data.CommandType.StoredProcedure;
         cmdRole.CommandText = "dbo.Get_RoleByUserID";
         cmdRole.Parameters.AddWithValue("@id", AssociateLogin.UserName);
         SqlDataReader rdr = cmdRole.ExecuteReader();
         while (rdr.Read())
         {
             HttpContext.Current.Session["Role"] = rdr["RoleDesc"].ToString();
             HttpContext.Current.Session["IsAdmin"] = rdr["IsAdmin"].ToString();
             HttpContext.Current.Session.Timeout = 15;
         }
         rdr.Close();
         Response.Redirect("~/default.aspx");
     }
     sqlConn.Close();
 }

        public bool altaRol(Rol rol)
        {
            using(TransactionScope ts = new TransactionScope())
            using(SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString))
            using (SqlCommand cmd = conn.CreateCommand())
                try
                {
                    cmd.CommandText = "INSERT INTO BONDIOLA.Rol (nombre) VALUES (@nombreRol)";
                    cmd.Parameters.Add(new SqlParameter("@nombreRol", rol.nombre));
                    conn.Open();
                    cmd.ExecuteNonQuery();

                    foreach (string funcionalidad in rol.funcionalidades)
                    {
                        SqlCommand cmdFuncionalidad = conn.CreateCommand();
                        cmdFuncionalidad.CommandType = CommandType.StoredProcedure;
                        cmdFuncionalidad.CommandText = "BONDIOLA.altaFuncionalidadRol";
                        cmdFuncionalidad.Parameters.AddWithValue("@funcionalidad", funcionalidad);
                        cmdFuncionalidad.Parameters.AddWithValue("@rol", rol.nombre);
                        cmdFuncionalidad.ExecuteNonQuery();
                    }
                    ts.Complete();
                    MessageBox.Show("Rol guardado con éxito.");
                    return true;
                }
                catch (SqlException ex)
                {
                    if (ex.Message.Contains("IX_Rol"))
                        MessageBox.Show("Ya existe un rol en el sistema con el nombre ingresado");
                    else MessageBox.Show(ex.Message);
                    return false;
                }
        }

        public void SetupFixture()
        {
            SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(UnitTests.Properties.Settings.Default.SqlServer2008);
            if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog))
            {
                Assert.Ignore("Requires SQL Server connectionstring");
            }

            GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();

            // Set up sample table
            using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008))
            {
                conn.Open();
                using(SqlCommand cmd = conn.CreateCommand())
                {
                    // The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
                    cmd.CommandText = "CREATE TABLE roads_ugl(ID decimal(10,0) identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)";
                    cmd.ExecuteNonQuery();
                }

                // Load data
                using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile()))
                {
                    shapeFile.Open();

                    IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());

                    indexes = indexes.Take(100);

                    foreach (uint idx in indexes)
                    {
                        var feature = shapeFile.GetFeatureByOid(idx);

                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "INSERT INTO roads_ugl(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))";

                            cmd.Parameters.AddWithValue("@Geom", feature.Geometry.AsText());
                            cmd.Parameters.AddWithValue("@Name", feature.Attributes["NAME"]);
                            cmd.Parameters.AddWithValue("@Srid", shapeFile.SRID);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                // Create spatial index
                using(SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "CREATE SPATIAL INDEX [IX_roads_ugl_GEOM] ON [dbo].[roads_ugl](GEOM)USING  GEOMETRY_GRID WITH (BOUNDING_BOX =(-98, 40, -82, 50), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM))";
                    cmd.ExecuteNonQuery();
                }
            }
        }

 public void SetupClass()
 {
     SqlConnection = new SqlConnection(@"Data Source=(localdb)\v11.0;Integrated Security=True");
     SqlConnection.Open();
     string createDatabase =
         string.Format("if not exists(select * from sys.databases where name = '{0}') CREATE DATABASE {0};", DataBaseName);
     var cmd = SqlConnection.CreateCommand();
     cmd.CommandText = createDatabase;
     cmd.ExecuteNonQuery();
     cmd = SqlConnection.CreateCommand();
     cmd.CommandText = "USE " + DataBaseName;
     cmd.ExecuteNonQuery();
 }

        public static XElement ModifyTelephoneIdentificationCode(Session session, Guid accountId, string oldCode, string newCode)
        {
            bool lastResult = false;
            try
            {
                Token token = SessionManager.Default.GetToken(session);
                string connectionString = SettingManager.Default.ConnectionString;
                using (SqlConnection sqlconnection = new SqlConnection(connectionString))
                {
                    SqlCommand sqlCommand = sqlconnection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "Account_UpdateDescription";
                    SqlCommandBuilder.DeriveParameters(sqlCommand);
                    sqlCommand.Parameters["@id"].Value = accountId;
                    sqlCommand.Parameters["@oldDescription"].Value = oldCode;
                    sqlCommand.Parameters["@newDescription"].Value = newCode;

                    sqlCommand.ExecuteNonQuery();
                    int result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
                    if (result == 0)
                    {
                        sqlCommand = sqlconnection.CreateCommand();
                        sqlCommand.CommandType = CommandType.Text;
                        sqlCommand.CommandText = string.Format("UPDATE AccountHistory SET UpdatePersonID = '{0}' WHERE ID = '{1}' AND [Description] = '{2}' AND UpdateTime = (SELECT MAX(UpdateTime) FROM AccountHistory WHERE ID='{1}' AND [Description] = '{2}')", token.UserID, accountId, newCode);
                        sqlCommand.ExecuteNonQuery();
                        lastResult = true;
                    }
                    else
                    {
                        //maybe the accountId is an employee id
                        sqlCommand = sqlconnection.CreateCommand();
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "Employee_UpdateTelephonePin";
                        sqlconnection.Open();
                        SqlCommandBuilder.DeriveParameters(sqlCommand);
                        sqlCommand.Parameters["@id"].Value = accountId;
                        sqlCommand.Parameters["@oldPin"].Value = oldCode;
                        sqlCommand.Parameters["@newPin"].Value = newCode;

                        sqlCommand.ExecuteNonQuery();
                        result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
                        lastResult = (result == 0);
                    }
                }
            }
            catch (System.Exception ex)
            {
                _Logger.Error(ex);
            }
            return XmlResultHelper.NewResult(lastResult.ToPlainBitString());
        }

        private void ConfigureDataAdapter()
        {
            con = new SqlConnection(cs);

            adapter.SelectCommand = con.CreateCommand();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = "DohvatiSveTablice";

            adapter.InsertCommand = con.CreateCommand();
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
            adapter.InsertCommand.CommandText = "DodajBolid";

            SqlParameter paramIDVozac = new SqlParameter();
            paramIDVozac.ParameterName = "@IDVozac";
            paramIDVozac.SqlDbType = SqlDbType.Int;
            paramIDVozac.SourceColumn = "IDVozac";
            SqlParameter paramNaziv = new SqlParameter();
            paramNaziv.ParameterName = "@Naziv";
            paramNaziv.SqlDbType = SqlDbType.NVarChar;
            paramNaziv.SourceColumn = "Naziv";

            adapter.InsertCommand.Parameters.Add(paramIDVozac);
            adapter.InsertCommand.Parameters.Add(paramNaziv);

            adapter.UpdateCommand = con.CreateCommand();
            adapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
            adapter.UpdateCommand.CommandText = "UpdateBolid";

            SqlParameter paramUpdateID = new SqlParameter();
            paramUpdateID.ParameterName = "@ID";
            paramUpdateID.SqlDbType = SqlDbType.Int;
            paramUpdateID.SourceColumn = "ID";
            SqlParameter paramUpdateNaziv = new SqlParameter();
            paramUpdateNaziv.ParameterName = "@Naziv";
            paramUpdateNaziv.SqlDbType = SqlDbType.NVarChar;
            paramUpdateNaziv.SourceColumn = "Naziv";

            adapter.UpdateCommand.Parameters.Add(paramUpdateNaziv);
            adapter.UpdateCommand.Parameters.Add(paramUpdateID);

            adapter.DeleteCommand = con.CreateCommand();
            adapter.DeleteCommand.CommandType = CommandType.StoredProcedure;
            adapter.DeleteCommand.CommandText = "BrisiBolid";

            SqlParameter paramDeleteID = new SqlParameter();
            paramDeleteID.ParameterName = "@ID";
            paramDeleteID.SqlDbType = SqlDbType.Int;
            paramDeleteID.SourceColumn = "ID";

            adapter.DeleteCommand.Parameters.Add(paramDeleteID);
        }