C# SqlDatabase.ExecuteNonQuery方法代码示例

        public int addNewUser(string firstname, string lastname, string companyname, string email, string password, string countryid, string stateid, string mobile, int type, string verify)
        {
            int status = 0;
            try
            {
                Guid guid = Guid.NewGuid();
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertUser";
                objDB.AddInParameter(objAdd, "@FName", DbType.String, firstname);
                objDB.AddInParameter(objAdd, "@LName", DbType.String, lastname);
                objDB.AddInParameter(objAdd, "@Companyname", DbType.String, companyname);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Password", DbType.String, password);
                objDB.AddInParameter(objAdd, "@Countryid", DbType.Int32, countryid);
                objDB.AddInParameter(objAdd, "@Stateid", DbType.Int32, stateid);
                objDB.AddInParameter(objAdd, "@Mobile", DbType.String, mobile);
                objDB.AddInParameter(objAdd, "@Type", DbType.Int32, type);
                objDB.AddInParameter(objAdd, "@Verify", DbType.String, verify);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - User Registration", "addNewUser", "GENERAL EXCEPTION");
                return status;
            }
        }

        public static bool BookTicketDAL(Models.TicketBooking data)
        {
            SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString);

            SqlCommand insertCmmnd = new SqlCommand("INSERT INTO TICKET_BOOKINGS ([Travel_Request_ID],[Ticket_Details],[Booking_Status]) VALUES (@Travel_Request_ID,@Ticket_Details,@Booking_Status)");
            insertCmmnd.CommandType = CommandType.Text;

            insertCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID);
            insertCmmnd.Parameters.AddWithValue("@Ticket_Details", data.Ticket_Details);
            insertCmmnd.Parameters.AddWithValue("@Booking_Status", data.Booking_Status);

            int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd);
            Console.Write("rowsAffected " + rowsAffected);

            SqlCommand updateCmmnd = new SqlCommand("UPDATE TRAVEL_REQUESTS SET [Request_Status]='P' WHERE [email protected]_Request_ID");
            updateCmmnd.CommandType = CommandType.Text;

            updateCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID);

            int rowsAffectedTReq = travelMSysDB.ExecuteNonQuery(updateCmmnd);

            //two booking records for same travel req issue to be resolved wherever applicable - or just delete the rows when booking cancelled
            if (rowsAffected == 1&&rowsAffectedTReq==1)
                return true;
            return false;
        }

        public int addInquiry(string name, string email, string subject, string message)
        {
            int status = 0;
            try
            {
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertInquiry";
                objDB.AddInParameter(objAdd, "@Name", DbType.String, name);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Subject", DbType.String, subject);
                objDB.AddInParameter(objAdd, "@Message", DbType.String, message);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objCommom.LogFile("Contact.aspx", "addInquiry", ex);
                return status;
            }
        }

        public void PublicarMensajeSql(string aplicacion, string error, Exception excepcion)
        {
            try
            {
                SqlDatabase baseDedatos = new SqlDatabase(ConfigurationManager.ConnectionStrings["AccesoDual"].ConnectionString);
                DbCommand comando = baseDedatos.GetStoredProcCommand("adm.NlayerSP_RegistrarErrorAplicativo");

                comando.CommandType = CommandType.StoredProcedure;

                string interna = null;

                if (excepcion.InnerException != null)
                {
                    interna = excepcion.InnerException.Message;
                }

                baseDedatos.AddInParameter(comando, "Aplicacion", SqlDbType.NVarChar, aplicacion);
                baseDedatos.AddInParameter(comando, "Error", SqlDbType.NVarChar, error);
                baseDedatos.AddInParameter(comando, "Excepcion", SqlDbType.NText, excepcion.Message);
                baseDedatos.AddInParameter(comando, "Interna", SqlDbType.NText, interna);

                baseDedatos.ExecuteNonQuery(comando);
            }
            catch {}
        }

        public void NoEventBroadcastIfNoEventRegistered()
        {
            string connectionString = @"server=(local)\sqlexpress;database=northwind;integrated security=true;";
            SqlDatabase db = new SqlDatabase(connectionString);

            db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region");
        }

        public static void SaveBaby(string babyName, long position, bool gender, int year, long rank)
        {
            Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
            using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Save"))
            {
                objDB.AddInParameter(objCMD, "@Name",
                                     DbType.String, babyName);
                objDB.AddInParameter(objCMD, "@Gender",
                                     DbType.String, gender.ToIndicator());
                objDB.AddInParameter(objCMD, "@Position",
                                    DbType.Int64, position);
                objDB.AddInParameter(objCMD, "@Rank",
                                     DbType.Int64, rank);
                objDB.AddInParameter(objCMD, "@Year",
                                    DbType.Int32, year);

                //objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public int addNewProductPost(string userid, int l1id, int l2id, int l3id, string productname, string keywords, string description, decimal quantity, decimal price, string image1,
            string image2, string image3, string image4)
        {
            int status = 0;
            try
            {

                Guid guid = new Guid(userid);
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertProductPost";
                objDB.AddInParameter(objAdd, "@USERID", DbType.Guid, guid);
                objDB.AddInParameter(objAdd, "@L1ID", DbType.Int32, l1id);
                objDB.AddInParameter(objAdd, "@L2ID", DbType.Int32, l2id);
                objDB.AddInParameter(objAdd, "@L3ID", DbType.Int32, l3id);
                objDB.AddInParameter(objAdd, "@PRODUCTNAME", DbType.String, productname);
                objDB.AddInParameter(objAdd, "@DESCRIPTION", DbType.String, description);
                objDB.AddInParameter(objAdd, "@QUANTITY", DbType.Decimal, quantity);
                objDB.AddInParameter(objAdd, "@PRICE", DbType.Decimal, price);
                objDB.AddInParameter(objAdd, "@KEYWORDS", DbType.String, keywords);
                objDB.AddInParameter(objAdd, "@IMAGE1", DbType.String, image1);
                objDB.AddInParameter(objAdd, "@IMAGE2", DbType.String, image2);
                objDB.AddInParameter(objAdd, "@IMAGE3", DbType.String, image3);
                objDB.AddInParameter(objAdd, "@IMAGE4", DbType.String, image4);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - Post Product", "addNewProductPost", "GENERAL EXCEPTION");
            }
            return status;
        }

        public static void UpdateRole(Role role)
        {
            string sqlQuery = "UPDATE ROLE SET [email protected] WHERE RoleID=" + role.RoleID;

            Database db = new SqlDatabase(DBHelper.GetConnectionString());
            DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
            db.AddInParameter(dbCommand, "Name", DbType.String, role.Name);
            db.ExecuteNonQuery(dbCommand);
        }

        //To write to DB
        public static int WriteToDb(string connectionString, string sqlQuery)
        {
            SqlDatabase db = new SqlDatabase(connectionString);

            DbCommand cmd = db.GetSqlStringCommand(sqlQuery);

            //return the number of rows affected
            return db.ExecuteNonQuery(cmd);
        }

 public static void Assign(int jobId, int userId)
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand cmd = db.GetStoredProcCommand("ALOC_Assign");
     db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
     db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
     db.ExecuteNonQuery(cmd);
     cmd.Dispose();
 }

        public static void UpdateCity(City city)
        {
            string sqlQuery = "UPDATE City SET [email protected] WHERE CityID=" + city.CityID;

            Database db = new SqlDatabase(DBHelper.GetConnectionString());
            DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
            db.AddInParameter(dbCommand, "Name", DbType.String, city);

            db.ExecuteNonQuery(dbCommand);
        }

 protected void btnupdate_Click(object sender, EventArgs e)
 {
     int test = 0;
     Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=d9deq4jc9e.database.windows.net;Database=kadsysv2;User id=jegan;Password=DBXPose!123;");
     //Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=bgtnhy\sql2008;Database=KadSysV2;User id=kaduser;Password=kad123;");
     test = objDataBase.ExecuteNonQuery(CommandType.Text, TextBox1.Text);
     objDataBase = null;
     Response.Write(test.ToString());
     Response.Write("Done");
 }

 public static bool DeleteEmployee(int employeeID)
 {
     string sqlQuery = "UPDATE EMPLOYEE SET Active=0 WHERE EmployeeID = " + employeeID.ToString();
     Database db = new SqlDatabase(DBHelper.GetConnectionString());
     DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
     if (db.ExecuteNonQuery(dbCommand) > 0)
         return true;
     else
         return false;
 }

 public void NoConnectionFailedEventBroadcastWithNoListener()
 {
     string connectionString = @"null;";
     SqlDatabase db = new SqlDatabase(connectionString);
     try
     {
         db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region");
     }
     catch (ArgumentException) {}
 }

		public void TestInitialize()
        {
            string partitionName = "Partition1";

            backingStore = CreateBackingStore(instanceName, partitionName);

			Data.Database db = new SqlDatabase(@"server=(local)\SQLEXPRESS;database=Caching;Integrated Security=true");
            DbCommand wrapper = db.GetSqlStringCommand("delete from CacheData");
            db.ExecuteNonQuery(wrapper);
        }