C# System.Data.SqlClient.SqlCommand.ExecuteNonQuery方法代码示例

        private void DoallThetracking(string trackkey, string value)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = "Server=devrystudentsp10.db.6077598.hostedresource.com;User Id=DeVryStudentSP10;Password=OidLZqBv4;";
            conn.Open();
            // Console.WriteLine(conn.State);
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;

            string SQL = "insert into huber_tracker12 (usertrackerid, trackkey, value,trackwhen) values (NEWID(), @trackkey, @trackvalue, @trackwhen)";

            comm.CommandText = SQL;

            comm.Parameters.AddWithValue("@trackkey", trackkey);
            comm.Parameters.AddWithValue("@trackvalue", value);
            comm.Parameters.AddWithValue("@trackwhen", DateTime.Now);

            comm.ExecuteNonQuery();

            SQL = "delete from huber_tracker12 where usertrackerid not in (select top 300 usertrackerid from huber_tracker12 order by trackwhen desc)";
                comm.CommandText = SQL;
            comm.ExecuteNonQuery();

            conn.Close();
        }

        public static bool ChangeProduct( System.Data.SqlClient.SqlConnection connection,
                                          System.Data.SqlClient.SqlTransaction tran,
                                          object old_product_id, object new_product_id, out string error)
        {
            bool done = false;
            error = "";
            try
            {
                connection.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                string query = "UPDATE Purchases.ReceiptContents SET ProductID = @NewProduct\n" +
                                "WHERE ProductID = @OldProduct";
                cmd.Parameters.AddWithValue("@NewProduct", new_product_id);
                cmd.Parameters.AddWithValue("@OldProduct", old_product_id);
                cmd.CommandTimeout = 0;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = query;
                cmd.Connection = connection;
                if (tran != null) cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                connection.Close();
                done = true;
            }
            catch (System.Exception ex)
            {
                error = ex.Message;
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open) connection.Close();
            }

            return done;
        }

 // Занесение новой записи в БД
 public static bool Insert(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, out string message)
 {
     bool done = false;
     message = "";
     try{
         connection.Open();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         string sQuery = "INSERT INTO " + Maker.Table + "\n" +
                         "       (MakerID, Name, MakerCategory, Vendor, Address, Created, Updated)\n" +
                         "VALUES (@MakerID, @Name, @MakerCategory, @Vendor, @Address, GETDATE(), GETDATE())";
         cmd.Parameters.AddWithValue("@MakerID", row["MakerID"]);
         cmd.Parameters.AddWithValue("@Name", row["Name"]);
         cmd.Parameters.AddWithValue("@MakerCategory", row["MakerCategory"]);
         cmd.Parameters.AddWithValue("@Vendor", row["Vendor"]);
         cmd.Parameters.AddWithValue("@Address", row["Address"]);
         //cmd.Parameters.AddWithValue("@Created", row["Created"]);
         //cmd.Parameters.AddWithValue("@Updated", row["Updated"]);
         cmd.Connection = connection;
         cmd.CommandTimeout = 0;
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = sQuery;
         cmd.ExecuteNonQuery();
         connection.Close();
         done = true;
     }catch (System.Exception ex){
         message = ex.Message;
     }finally{
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return done;
 }

 private void AutoSignOff()
 {
     try
     {
         bool autoOff = bool.Parse(ConfigurationManager.AppSettings["AutoSignOff"]);
         int autoOffTime = int.Parse(ConfigurationManager.AppSettings["AutoSignOffMinutes"]);
         string time = DateTime.Now.AddMinutes(-autoOffTime).ToString("yyyy-MM-dd HH:mm:ss");
         if (!autoOff)
             return;
         try
         {
             var con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Entities"].ConnectionString);
             var com = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM UserOnline WHERE [TimeStamp] < '{0}'", time), con);
             con.Open();
             com.ExecuteNonQuery();
             try
             {
                 con.Close();
             }
             catch
             {
             }
         }
         catch
         {
         }
     }
     catch
     {
     }
 }

        //Metodo de Iserción de Datos
        public bool Insertar(Comun.Vars p)
        {
            bool Resultado = false;
               //Realizar metodo de conexión
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
            //Activa o Desactivar String de Conexion
            cn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Wayvas\\Task\\Presentacion\\App_Data\\Task.mdf;Integrated Security=True;User Instance=True";
            //cn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=True";
            cn.Open();//Iniciamos la Conexion

            System.Data.SqlClient.SqlCommand cm =new System.Data.SqlClient.SqlCommand();
            cm.Connection=cn;
            cm.CommandType=System.Data.CommandType.Text;
            cm.CommandText = "Insert into Task(TaskName,StartDate,DueDate,CompDate,TaskComs) Values(@P_TaskName,@P_StartDate,@P_DueDate,@P_CompDate,@P_TaskComs)";

            cm.Parameters.AddWithValue("@P_TaskName", p.TaskName);
            cm.Parameters.AddWithValue("@P_StartDate", p.StartDate);
            cm.Parameters.AddWithValue("@P_DueDate", p.DueDate);
            cm.Parameters.AddWithValue("@P_CompDate", p.CompDate);
            cm.Parameters.AddWithValue("@P_TaskComs", p.TaskComs);

            int ra = cm.ExecuteNonQuery();
                   if (ra==1)
                   { Resultado=true;}
            cn.Close();
            cn.Dispose();
            return Resultado;
        }

 public static int DeleteFromSqlTable(string tableName, string columnName, string key, System.Data.SqlClient.SqlConnection connection)
 {
     System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
     cmd.CommandText = "delete from " + tableName + " where " + columnName + " = " + key;
     cmd.Connection = connection;
     return cmd.ExecuteNonQuery();
 }

        private void btnSave_Click(object sender, EventArgs e)
        {
            string connectionstring = "Data Source=devrystudentsp10.db.6077598.hostedresource.com;Persist Security Info=True;User ID=DeVryStudentSP10;Password=OidLZqBv4";
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = connectionstring;
            conn.Open();
            //MessageBox.Show(conn.State.ToString());
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "insert into Huber_Logon (email, lastname, firstname, password, userid)";
            comm.CommandText += " values (@email, @lastname, @firstname, @password, @userid)";

            comm.Parameters.AddWithValue("@email", txtemail.Text);
            comm.Parameters.AddWithValue("@lastname", txtLname.Text);
            comm.Parameters.AddWithValue("@firstname", txtfname.Text);
            comm.Parameters.AddWithValue("@password", txtpassword.Text);
            comm.Parameters.AddWithValue("@userid", txtuserid.Text);
            if (!dbLayer.isuseralreadyinthedbornot(txtuserid.Text, connectionstring))
            {
                comm.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("You Must enter another userid, that one is in use!");
            }

            conn.Close();
            conn.Dispose();
        }

 public static bool Delete(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, out string message)
 {
     bool done = false;
     message = "";
     try
     {
         connection.Open();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         string sQuery = "DELETE FROM " + Categories.Table + "\n" +
                         " WHERE CategoryID = @CategoryID";
         cmd.Parameters.AddWithValue("@CategoryID", row["CategoryID"]);
         cmd.Connection = connection;
         cmd.CommandTimeout = 0;
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = sQuery;
         cmd.ExecuteNonQuery();
         connection.Close();
         done = true;
     }
     catch (System.Exception ex)
     {
         message = ex.Message;
     }
     finally
     {
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return done;
 }

        public void InsertError(Exception err, int refId)
        {
            string conn = "Data Source=diyfe.org;Initial Catalog=MLB;Persist Security Info=True;User ID=jbt411;Password=ZigZag15";
            string queryString = "INSERT INTO [MLB].[dbo].[Error] ([RefId],[ErrorMethod],[ErrorText],[ErrorDate]) VALUES (@RefId,@ErrorMethod,@ErrorText,@ErrorDate)";

            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(conn))
            {
                // Create the Command and Parameter objects.
                System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@RefId", refId);
                if (err.Source == null)
                { err.Source = "none"; }
                command.Parameters.AddWithValue("@ErrorMethod", err.Source);

                command.Parameters.AddWithValue("@ErrorText", "Excpetion: " + err.Message + "</br>Inner Excpetion: " + err.InnerException);
                command.Parameters.AddWithValue("@ErrorDate", DateTime.Now);

                try
                {
                    var email = EmailMessageFactory.GetErrorEmail(err);
                    var result = EmailClient.SendEmail(email);

                    connection.Open();
                    command.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    var email = EmailMessageFactory.GetErrorEmail(ex);
                    var result = EmailClient.SendEmail(email);
                }
            }
        }

        protected void ExecutionConfirmed(object sender, EventArgs e)
        {
            panelMain.Visible = true;
            confirmPanel.Visible = false;

            System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController("EOBProcessing");

            try
            {
                sc.Stop();
                System.Threading.Thread.Sleep(5000);

                dbProcedures db = new dbProcedures();
                System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("usp_AddOneTimeRunSchedule", new db().SqlConnection);
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

                sqlCmd.ExecuteNonQuery();
                db.Close();

                sc.Start();
                lblMessage.Text = "EOB System manual execution started.";

            }
            catch (Exception e1)
            {
                lblMessage.Text = "Failed to manually start EOB System service." + e1.Message();
            }
        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    string path = String.Format("{0}\\{1}PharmaInventory{2:MMMddyyyy}.bak", folderBrowserDialog1.SelectedPath, GeneralInfo.Current.HospitalName, DateTimeHelper.ServerDateTime);

                    string connectionString = readApp.GetValue("dbConnection", typeof(string)).ToString();

                    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
                    System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand();

                    if (!(conn.State == ConnectionState.Open))
                        conn.Open();
                    string dbName = conn.Database;
                    com.CommandText = "BACKUP DATABASE [" + dbName + "] TO  DISK = N'" + path + "' WITH NOFORMAT, NOINIT,  NAME = N'PharmaInventory" + DateTimeHelper.ServerDateTime.ToString("MMMddyyyy") + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
                    com.Connection = conn;
                    com.ExecuteNonQuery();

                    GeneralInfo.Current.LastBackUp = DateTimeHelper.ServerDateTime;
                    GeneralInfo.Current.Save();
                    MessageBox.Show(@"Backup completed to " + path + @"!", @"Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch
                {
                    MessageBox.Show(@"Backup has failed! Please Try Again.",@"Try Again",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
            }
        }

 public static bool Delete(System.Data.SqlClient.SqlConnection connection,
                           System.Data.SqlClient.SqlTransaction tran,
                           System.Data.DataRow row, out string message)
 {
     bool done = false;
     message = "";
     try
     {
         connection.Open();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         string sQuery = "DELETE FROM Producer.Products\n" +
                         " WHERE ProductID = @ProductID";
         cmd.Parameters.AddWithValue("@ProductID", row["ProductID"]);
         cmd.Connection = connection;
         if (tran != null) cmd.Transaction = tran;
         cmd.CommandTimeout = 0;
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = sQuery;
         cmd.ExecuteNonQuery();
         connection.Close();
         done = true;
     }
     catch (System.Exception ex)
     {
         message = ex.Message;
     }
     finally
     {
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return done;
 }

        /// <summary>
        /// �������� ������ � �� � ���������� �����
        /// </summary>
        /// <param name="connection">��������� � ��</param>
        /// <param name="row">������</param>
        /// <param name="message">�������� ��������� �� ������, ���� ����� ���������� ����</param>
        /// <returns>����� ���������� ������, ���� �� �������� ������; ���� - � ��������� ������</returns>
        public static bool Delete(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, /*byte[] image,*/ out string message)
        {
            bool done = false;
            message = "";
            try{
                connection.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                string sQuery = "IF EXISTS (SELECT DiscountCard\n" +
                                "             FROM Purchases.Receipts\n" +
                                "            WHERE Purchases.Receipts.DiscountCard = @CardID ) \n" +
                                "   UPDATE Purchases.DiscountCards\n" +
                                "      SET Expired = GETDATE()\n" +
                                "    WHERE CardID = @CardID\n" +
                                "ELSE\n" +
                                "   DELETE\n" +
                                "     FROM Purchases.DiscountCards\n" +
                                "    WHERE CardID = @CardID";

                cmd.Parameters.AddWithValue("@CardID", row["CardID"]);
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sQuery;
                cmd.ExecuteNonQuery();
                connection.Close();
                done = true;
            }catch (System.Exception ex){
                message = ex.Message;
            }finally{
                if (connection.State == System.Data.ConnectionState.Open) connection.Close();
            }
            return done;
        }

        public void InsertError(Exception err, int refId, bool sendMail)
        {
            string conn = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnection"].ConnectionString;
            string queryString = "INSERT INTO [MLB].[dbo].[Error] ([RefId],[ErrorMethod],[ErrorText],[ErrorDate]) VALUES (@RefId,@ErrorMethod,@ErrorText,@ErrorDate)";

            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(conn))
            {
                // Create the Command and Parameter objects.
                System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@RefId", refId);
                command.Parameters.AddWithValue("@ErrorMethod", err.Source);
                command.Parameters.AddWithValue("@ErrorText", err.InnerException);
                command.Parameters.AddWithValue("@ErrorDate", DateTime.Now);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                { }
            }

            if (sendMail)
            {
                var email = EmailMessageFactory.GetErrorEmail(err);
                var result = EmailClient.SendEmail(email);
            }
        }

        protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
        {
            String sqlstring;
               // sqlstring = "insert into sys_user values ((select max(sysuser_id)+1 from sys_user),'" +
               //     CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password + "','1', GETDATE(), 3);";

            //SQL 2005 version
            sqlstring = "insert into sys_user select max(sysuser_id)+1, '" +
                CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password +
                "', GETDATE(), (Select top 1 sysrole_id from sys_role where sysrole_name='Applicant') from sys_user ";

            // create a connection with sqldatabase
            //System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
             //            " Data Source=SD;Initial Catalog=SRS;User ID=sa;Password=Ab123456;Connect Timeout=10;TrustServerCertificate=True ");
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
                WebConfigurationManager.ConnectionStrings["SRSDB"].ConnectionString);
            // create a sql command which will user connection string and your select statement string
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring, con);

            // create a sqldatabase reader which will execute the above command to get the values from sqldatabase
            try
            {
                con.Open();
                if (comm.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                con.Close();
            }
        }