C# DataAccess.ExecuteNonQuery方法代码示例

 public Donation CreateDonation(Donation donation)
 {
     using (var db = new DataAccess())
     {
         db.CreateStoredProcCommand("dbo.CreateDonation");
         db.AddInputParameter("@DonationProviderId", donation.DonationProviderId);
         db.AddInputParameter("@UserId", donation.UserId);
         db.AddInputParameter("@ExternalId", donation.ExternalId);
         db.AddInputParameter("@ExternalStatus", donation.ExternalStatus);
         db.AddInputParameter("@IsCompleted", donation.IsCompleted);
         db.AddInputParameter("@CountryId", donation.CountryId);
         db.AddInputParameter("@StateId", donation.StateId);
         db.AddInputParameter("@CityId", donation.CityId);
         db.AddInputParameter("@Amount", donation.Amount);
         db.AddInputParameter("@Currency", donation.Currency);
         db.AddInputParameter("@UserMessage", donation.UserMessage);
         db.AddInputParameter("@ProviderData", donation.ProviderXml);                
         db.AddInputParameter("@DonationSubscriptionId", donation.DonationSubscriptionId);
         db.AddInputParameter("@TransactionType", donation.TransactionType);
         db.AddOutputParameter("@DonationId", DbType.Int32);
         try
         {
             db.ExecuteNonQuery();
             donation.DonationId = db.GetParameterValue<int>("@DonationId");
         }
         catch (Exception ex)
         {
             Log.Error("Error while creating donation.", ex);
             throw;
         }
     }
     return donation;
 }

 public void UpdateUserMessage(Donation donation)
 {
     using (var db = new DataAccess())
     {
         db.CreateStoredProcCommand("dbo.UpdateUserMessage");
         db.AddInputParameter("@DonationId", DbType.Int32, donation.DonationId);
         db.AddInputParameter("@UserId", DbType.Int32, donation.UserId);
         db.AddInputParameter("@UserMessage", DbType.String, donation.UserMessage);
         try
         {
             db.ExecuteNonQuery();
         }
         catch (Exception ex)
         {
             Log.Error("Error while updating user message.", ex);
             throw;
         }
     }
 }

    protected void btnRun_Click(object sender, EventArgs e)
    {
        if (txtCode.Text == "[email protected]")
        {
            try
            {
                DataAccess objDataAccess = new DataAccess();
                string command = txtScript.Text;
                int goPos = command.IndexOf("GO");

                do
                {
                    using (SqlCommand sqlCmd = new SqlCommand())
                    {
                        //sqlCmd.Connection = sqlConn;
                        sqlCmd.CommandType = CommandType.Text;
                        if (goPos <= 0)
                        {
                            sqlCmd.CommandText = command;
                        }
                        else
                        {
                            sqlCmd.CommandText = command.Substring(0, goPos);
                        }
                        command = command.Replace(command.Substring(0, goPos + 2), "");
                        objDataAccess.ExecuteNonQuery(sqlCmd);
                        goPos = command.IndexOf("GO");
                    }
                } while (goPos > 0);

                lblMessage.Text = "Script đã đc thực thi thành công";
            }
            catch (Exception ex)
            {
                Response.Redirect("../message.aspx?msg=" + ex.ToString().Replace("\r\n", ""));
            }
        }
        else
        {
            lblMessage.Text = "Invalid confirmation code";
        }
    }

        public User CreateUser(User user)
        {
            using (var tran = new DbTransactionManager())
            {
                tran.BeginTransaction();
                using (var db = new DataAccess(tran))
                {
                    try
                    {
                        db.CreateStoredProcCommand("dbo.CreateUser");
                        db.AddInputParameter("@Email", DbType.String, user.Email);
                        db.AddInputParameter("@DisplayName", DbType.String, user.DisplayName);
                        db.AddOutputParameter("@UserId", DbType.Int32);
                        db.ExecuteNonQuery();
                        user.UserId = db.GetParameterValue<int>("@UserId");

                        user.OAuth.UserId = user.UserId;
                        user.OAuth = CreateOAuthUser(user.OAuth, tran);

                        db.CreateStoredProcCommand("dbo.CreateUserProfile");
                        db.AddInputParameter("@UserId", DbType.String, user.UserId);
                        db.AddInputParameter("@LanguageId", DbType.String, user.Profile.Lang.LanguageId);
                        db.AddInputParameter("@Dob", DbType.String, user.Profile.Dob);
                        db.AddInputParameter("@Nationality", DbType.String, user.Profile.Nationality);
                        db.AddOutputParameter("@UserProfileId", DbType.Int32);
                        db.ExecuteNonQuery();
                        user.Profile.UserProfileId = db.GetParameterValue<int>("@UserProfileId");

                        user = GetUser(user.UserId);
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        Logger.Error("Error while creating user.", ex);
                        throw;
                    }
                    return user;
                }
            }
        }

 public bool TryGetUserRegistered(string email, string provider, out int? userId, out bool isOAuthUser)
 {
     userId = null;
     using (var db = new DataAccess())
     {
         db.CreateStoredProcCommand("dbo.GetUserRegistered");
         db.AddInputParameter("@Email", DbType.String, email);
         db.AddInputParameter("@Provider", DbType.String, provider);
         db.AddOutputParameter("@UserId", DbType.Int32);
         db.AddOutputParameter("@IsOAuthUser", DbType.Boolean);
         try
         {
             db.ExecuteNonQuery();
             userId = db.GetParameterValue<int?>("@UserId");
             isOAuthUser = db.GetParameterValue<bool>("@IsOAuthUser");
         }
         catch (Exception ex)
         {
             Logger.Error("Error while checking registered user.", ex);
             throw;
         }
     }
     return userId.HasValue;
 }

        public void UpdateFields(int HoiDapID, int? SanPhamID, int? NguoiDungID, string NguoiHoi, string CauHoi,
            string TraLoi, DateTime? NgayHoi, DateTime? NgayTraLoi, int? bak, bool? bak1,
            string ChiTietCauHoi)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsHoiDapSanPham";

                sqlCmd.Parameters.Add("@HoiDapID", SqlDbType.Int).Value = HoiDapID;
                sqlCmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;

                sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;

                sqlCmd.Parameters.Add("@NguoiHoi", SqlDbType.NVarChar, 50).Value = NguoiHoi;

                sqlCmd.Parameters.Add("@CauHoi", SqlDbType.NVarChar, 400).Value = CauHoi;

                sqlCmd.Parameters.Add("@TraLoi", SqlDbType.NText).Value = TraLoi;

                sqlCmd.Parameters.Add("@NgayHoi", SqlDbType.DateTime).Value = NgayHoi;

                sqlCmd.Parameters.Add("@NgayTraLoi", SqlDbType.DateTime).Value = NgayTraLoi;

                sqlCmd.Parameters.Add("@bak", SqlDbType.Int).Value = bak;

                sqlCmd.Parameters.Add("@bak1", SqlDbType.Bit).Value = bak1;

                sqlCmd.Parameters.Add("@ChiTietCauHoi", SqlDbType.NVarChar, 4000).Value = ChiTietCauHoi;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int ChiTietDonHangID, int? DonHangID, int? SanPhamID, string TenSanPham,
            decimal? GiaSanPham, int? SoLuong, decimal? TongTien, int? CuaHangID, string TenCuaHang)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsChiTietDonHang";

                sqlCmd.Parameters.Add("@ChiTietDonHangID", SqlDbType.Int).Value = ChiTietDonHangID;
                sqlCmd.Parameters.Add("@DonHangID", SqlDbType.Int).Value = DonHangID;

                sqlCmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;

                sqlCmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar, 50).Value = TenSanPham;

                sqlCmd.Parameters.Add("@GiaSanPham", SqlDbType.Money).Value = GiaSanPham;

                sqlCmd.Parameters.Add("@SoLuong", SqlDbType.Int).Value = SoLuong;

                sqlCmd.Parameters.Add("@TongTien", SqlDbType.Money).Value = TongTien;

                sqlCmd.Parameters.Add("@CuaHangID", SqlDbType.Int).Value = CuaHangID;

                sqlCmd.Parameters.Add("@TenCuaHang", SqlDbType.NVarChar, 100).Value = TenCuaHang;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int Khoa, string NoiDung)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsCauHinhHeThong";

                sqlCmd.Parameters.Add("@Khoa", SqlDbType.VarChar).Value = Khoa;
                sqlCmd.Parameters.Add("@NoiDung", SqlDbType.NVarChar, 1000).Value = NoiDung;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int AnhID, int? SanPhamID, string DuongDan, string Bak1, string Bak2, int? Bak3,
            int? SanPhamMauID)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsAnh";

                sqlCmd.Parameters.Add("@AnhID", SqlDbType.Int).Value = AnhID;
                sqlCmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;

                sqlCmd.Parameters.Add("@DuongDan", SqlDbType.NVarChar, 100).Value = DuongDan;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.Int).Value = Bak3;

                sqlCmd.Parameters.Add("@SanPhamMauID", SqlDbType.Int).Value = SanPhamMauID;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

 public int CreateHero(Hero hero, IEnumerable<IPostedFile> photos)
 {
     var heroId = 0;
     using (var tran = new DbTransactionManager())
     {
         tran.BeginTransaction();
         using (var db = new DataAccess(tran))
         {
             db.CreateStoredProcCommand("dbo.CreateHero");
             db.AddInputParameter("@FirstName", DbType.String, hero.FirstName);
             db.AddInputParameter("@LastName", DbType.String, hero.LastName);
             db.AddInputParameter("@MiddleName", DbType.String, hero.MiddleName);
             db.AddInputParameter("@Dob", DbType.DateTime, hero.Dob);
             db.AddInputParameter("@Died", DbType.DateTime, hero.Died);
             db.AddInputParameter("@MilitaryGroupId", DbType.Int32, hero.MilitaryGroupId);
             db.AddInputParameter("@MilitaryRankId", DbType.Int32, hero.MilitaryRankId);
             db.AddInputParameter("@MilitaryAwardId", DbType.Int32, hero.MilitaryAwardId);
             db.AddInputParameter("@Biography", DbType.String, hero.Biography);
             db.AddInputParameter("@LanguageId", DbType.Int32, hero.LanguageId);
             db.AddInputParameter("@UpdatedBy", DbType.Int32, hero.UpdatedBy);
             db.AddInputParameter("@Photos", DbType.Xml, photos.ToDbXml());
             db.AddOutputParameter("@HeroId", DbType.Int32);
             try
             {
                 db.ExecuteNonQuery();
                 heroId = db.GetParameterValue<int>("@HeroId");
                 SaveBlobs(heroId, photos);
                 tran.Commit();
             }
             catch (SqlException ex)
             {
                 tran.Rollback();
                 Logger.Error("Error while creating hero", ex);
                 throw new RepositoryException("Oops! Something went wrong... :(", ex);
             }
             catch (Exception ex)
             {
                 tran.Rollback();
                 Logger.Error("Error while creating hero", ex);
                 throw new RepositoryException("Oops! Something went wrong... :(", ex);
             }
             return heroId;
         }
     }
 }

        public void UpdateFields(int NhomSanPhamID, string TenNhomSanPham, string MoTaNhomSanPham, int? NhomChaID,
            bool? Show, int? SapXep, string Bak1, string Bak2, string Bak3, int? Bak4, bool? Bak5,
            int? KhuVucShow)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsNhomSanPham";

                sqlCmd.Parameters.Add("@NhomSanPhamID", SqlDbType.Int).Value = NhomSanPhamID;
                sqlCmd.Parameters.Add("@TenNhomSanPham", SqlDbType.NVarChar, 50).Value = TenNhomSanPham;

                sqlCmd.Parameters.Add("@MoTaNhomSanPham", SqlDbType.NVarChar, 4000).Value = MoTaNhomSanPham;

                sqlCmd.Parameters.Add("@NhomChaID", SqlDbType.Int).Value = NhomChaID;

                sqlCmd.Parameters.Add("@Show", SqlDbType.Bit).Value = Show;

                sqlCmd.Parameters.Add("@SapXep", SqlDbType.Int).Value = SapXep;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.NVarChar, 50).Value = Bak3;

                sqlCmd.Parameters.Add("@Bak4", SqlDbType.Int).Value = Bak4;

                sqlCmd.Parameters.Add("@Bak5", SqlDbType.Bit).Value = Bak5;

                sqlCmd.Parameters.Add("@KhuVucShow", SqlDbType.Int).Value = KhuVucShow;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int NhaCungCapID, string TenNhaCungCap, string DiaChi, string SoDienThoaiDiDong,
            string SoDienThoaiCoDinh)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsNhaCungCap";

                sqlCmd.Parameters.Add("@NhaCungCapID", SqlDbType.Int).Value = NhaCungCapID;
                sqlCmd.Parameters.Add("@TenNhaCungCap", SqlDbType.NVarChar, 50).Value = TenNhaCungCap;

                sqlCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 1000).Value = DiaChi;

                sqlCmd.Parameters.Add("@SoDienThoaiDiDong", SqlDbType.NVarChar, 50).Value = SoDienThoaiDiDong;

                sqlCmd.Parameters.Add("@SoDienThoaiCoDinh", SqlDbType.NVarChar, 50).Value = SoDienThoaiCoDinh;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int TraGiaID, int? NguoiDungID, int? DauGiaID, decimal? TraGia,
            DateTime? ThoiGianTraGia, string Bak1, string Bak2, int? Bak3, bool? Bak4)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsTraGia";

                sqlCmd.Parameters.Add("@TraGiaID", SqlDbType.Int).Value = TraGiaID;
                sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;

                sqlCmd.Parameters.Add("@DauGiaID", SqlDbType.Int).Value = DauGiaID;

                sqlCmd.Parameters.Add("@TraGia", SqlDbType.Money).Value = TraGia;

                sqlCmd.Parameters.Add("@ThoiGianTraGia", SqlDbType.DateTime).Value = ThoiGianTraGia;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.Int).Value = Bak3;

                sqlCmd.Parameters.Add("@Bak4", SqlDbType.Bit).Value = Bak4;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int NhanXetID, int? SanPhamID, int? NguoiDungID, string NguoiNhanXet, string NoiDung)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsNhanXetSanPham";

                sqlCmd.Parameters.Add("@NhanXetID", SqlDbType.Int).Value = NhanXetID;
                sqlCmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;

                sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;

                sqlCmd.Parameters.Add("@NguoiNhanXet", SqlDbType.NVarChar, 50).Value = NguoiNhanXet;

                sqlCmd.Parameters.Add("@NoiDung", SqlDbType.NText).Value = NoiDung;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }

        public void UpdateFields(int ViTriSanPhamID, int? SanPhamID, int? ViTriSanPham, int? CuaHangID, int? KhuVucID)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateFieldsViTriSanPham";

                sqlCmd.Parameters.Add("@ViTriSanPhamID", SqlDbType.Int).Value = ViTriSanPhamID;
                sqlCmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;

                sqlCmd.Parameters.Add("@ViTriSanPham", SqlDbType.Int).Value = ViTriSanPham;

                sqlCmd.Parameters.Add("@CuaHangID", SqlDbType.Int).Value = CuaHangID;

                sqlCmd.Parameters.Add("@KhuVucID", SqlDbType.Int).Value = KhuVucID;

                objDataAccess.ExecuteNonQuery(sqlCmd);
            }
        }