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

        public static void Insert(int idWebmail, int idSequence, string mailTo, string errorMessage, string subject, string body, bool sendSucess, string extra, int idSystem, int idUserCreate, string cdTemplate, string cdIdentification1, string cdIdentification2, string cdIdentification3)
        {
            System.Data.SqlClient.SqlConnection connection = null;
            System.Data.SqlClient.SqlCommand command = null;

            connection = new System.Data.SqlClient.SqlConnection(ConnectionStrings);

            command = new System.Data.SqlClient.SqlCommand(Schema + ".Sys_WebmailLogInsert", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@idWebmail", System.Data.SqlDbType.Int).Value = idWebmail;
            command.Parameters.Add("@idSequence", System.Data.SqlDbType.Int).Value = idSequence;
            command.Parameters.Add("@idSystem", System.Data.SqlDbType.Int).Value = idSystem;
            command.Parameters.Add("@dsMailTo", System.Data.SqlDbType.VarChar, 500).Value = mailTo;
            command.Parameters.Add("@dsError", System.Data.SqlDbType.VarChar, 4500).Value = errorMessage;
            command.Parameters.Add("@dsSubject", System.Data.SqlDbType.VarChar, 500).Value = subject;
            command.Parameters.Add("@dsMessage", System.Data.SqlDbType.VarChar, 4500).Value = body;
            command.Parameters.Add("@dsExtra", System.Data.SqlDbType.VarChar, 4500).Value = extra;
            command.Parameters.Add("@cdTemplate", System.Data.SqlDbType.VarChar, 100).Value = cdTemplate;
            command.Parameters.Add("@cdIdentification1", System.Data.SqlDbType.VarChar, 100).Value = cdIdentification1;
            command.Parameters.Add("@cdIdentification2", System.Data.SqlDbType.VarChar, 100).Value = cdIdentification2;
            command.Parameters.Add("@cdIdentification3", System.Data.SqlDbType.VarChar, 100).Value = cdIdentification3;
            command.Parameters.Add("@flSent", System.Data.SqlDbType.Bit).Value = sendSucess;
            command.Parameters.Add("@idUserCreate", System.Data.SqlDbType.Int).Value = idUserCreate;

            connection.Open();
            command.ExecuteScalar();
        }

 public static bool Exists(System.Data.SqlClient.SqlConnection connection, string category_name, out bool exists, out string message)
 {
     bool ret = false;
     exists = false;
     message = "";
     Guid cat_id = Guid.Empty;
     try
     {
         connection.Open();
         string sQuery = "SELECT CategoryID\n" +
                         "  FROM " + Categories.Table + "\n" +
                         " WHERE CategoryName = @Category";
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sQuery);
         cmd.Parameters.AddWithValue("@Category", category_name );
         cmd.Connection = connection;
         object res = cmd.ExecuteScalar();
         if (res != null && !System.Convert.IsDBNull(res)) cat_id = (Guid)res;
         connection.Close();
         exists = (cat_id != Guid.Empty);
         ret = true;
     }
     catch (System.Exception ex)
     {
         message = ex.Message;
     }
     finally
     {
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return ret;
 }

 public Home()
 {
     this.WebConfig = System.Configuration.ConfigurationManager.AppSettings["Setting"];
     this.Build = System.Configuration.ConfigurationManager.AppSettings["Build"];
     this.Version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(4);
     bool executeDB = false;
     #if AZURE
     this.WebRoleConfig = Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("Environment");
     executeDB = Convert.ToBoolean(Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("executeDB"));
     #else
     executeDB = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["ExecuteDB"]);
     #endif
     string conn;
     #if AZURE
     conn = Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("DBConn");
     #else
     conn = System.Configuration.ConfigurationManager.ConnectionStrings["DBConn"].ToString(); ;
     #endif
     if (executeDB)
     {
         var cn = new System.Data.SqlClient.SqlConnection(conn);
         cn.Open();
         var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 1 Col1 FROM Items", cn);
         this.DatabaseValue = cmd.ExecuteScalar().ToString();
         cn.Close();
     }
 }

    public bool Save(string CodAntena, string TagEquipo)
    {
      // Aca se graba la informacion en la base de datos.

      //1. Para el caso RFID, donde del Agente está escuchando o leyendo los Data Logger cada 15 segundos se debe ejecutar la siguiente SP con los parámetros que se detallan:
      //-----------------------------------------------------------------------------------------------------------------------
      //-- Declare type parameter
      //DECLARE @RC int                                                   --Valor de retorno 0=OK
      //DECLARE @CODANTENA varchar(4)                         --Código de antena
      //DECLARE @TAGEQUIPO varchar(16)                        --Nro de Tag del equipo/camión
      //-- Set parameter values
      //SET @CODANTENA = '0001'
      //SET @TAGEQUIPO = 'A000000000000112'
      //EXEC @RC = [TARJA].[dbo].[STP_MOVIMIENTOS_PUNTOS_CONTROL] @CODANTENA, @TAGEQUIPO
      //-----------------------------------------------------------------------------------------------------------------------
      // Para los códigos de las antenas determinamos la siguiente codificación:
      //                       1 = Trapiche 1
      //2 = Trapiche 2
      //3 = Trapiche 3
      //4 = Bascula 9
      //5 = Bascula 10
      //6 = Portería Gas
      //7 = Portería Gas Interno)

      try
      {
        string strConnection = GetConnectionString();
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConnection);
        conn.Open();

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "STP_MOVIMIENTOS_PUNTOS_CONTROL";

        System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@CODANTENA", System.Data.SqlDbType.VarChar, 4);
        prm.Value = CodAntena;
        cmd.Parameters.Add(prm);

        prm = new System.Data.SqlClient.SqlParameter("@TAGEQUIPO", System.Data.SqlDbType.VarChar, 16);
        prm.Value = TagEquipo;
        cmd.Parameters.Add(prm);

        int rc =(int) cmd.ExecuteScalar();

        if (rc == 0)
          return true;
        else
          return false;
      }
      catch (Exception)
      {
        return false;
      }
    }

        protected void cmdAddUser_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HuberTrackerConnection"].ToString();
            conn.ConnectionString = connectionstring;
            conn.Open();
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;

            string sql = "select userid from huber_logon where userid = @userid";

            comm.CommandText = sql;

            comm.Parameters.AddWithValue("@userid", txtUserName.Text);

            object result = comm.ExecuteScalar();
            if (result != null)
            {
              //that userid exists! ABORT! (and tell the user)
                lblError.Text = "That username is already in use.";
                lblError.Visible = true;

            }
            else
            {
                sql = "insert into huber_logon (userid, password, firstname, lastname, email) values (@userid, @password, @firstname, @lastname, @email)";
                comm.CommandText = sql;
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("@userid", txtUserName.Text);
                comm.Parameters.AddWithValue("@password", txtPassword.Text);
                comm.Parameters.AddWithValue("@firstname", txtFirstName.Text);
                comm.Parameters.AddWithValue("@lastname", txtLastName.Text);
                comm.Parameters.AddWithValue("@email", txtEmail.Text);

                int numrowsaffected;
                numrowsaffected = comm.ExecuteNonQuery();
                if (numrowsaffected == 1)
                {
                  //  lblError.Text = "User Added!";
                    lblError.Visible = true;
                    ClearFields();
                   lblError.Text =  EmailThatUser(txtEmail.Text);
                   lblError.Text += "cockroach";
                }
                else
                {
                    lblError.Text = "There was a problem with the user!";
                    lblError.Visible = true;
                }
               }
            conn.Close();
        }

        /// <summary>
        /// 获取数据库当前时间
        /// </summary>
        public override DateTime GetDateTimeNow()
        {
            DateTime ret = DateTime.Now;
            using(System.Data.SqlClient.SqlConnection con=new System.Data.SqlClient.SqlConnection(this.ConnectionText))
            {
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select getdate()",con))
                {
                    con.Open();
                    ret = Convert.ToDateTime(cmd.ExecuteScalar());
                    con.Close();
                }
            }

            return ret;
        }

        protected void cmdLogIn_Click(object sender, EventArgs e)
        {
            ////web config method
            //if (System.Web.Security.FormsAuthentication.Authenticate(txtUserName.Text, txtPassword.Text))
            //{
            //    //good you logged in.
            //    System.Web.Security.FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, false);
            //}
            //else
            //{
            //    lblError.Text = "Either your username or password was incorrect";
            //    lblError.Visible = true;
            //}
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HuberTrackerConnection"].ToString();
            conn.ConnectionString = connectionstring;
            conn.Open();
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;

            string sql = "select userlevel from huber_logon where userid = @userid and password = @password";

            comm.CommandText = sql;

            comm.Parameters.AddWithValue("@userid", txtUserName.Text);
            comm.Parameters.AddWithValue("@password", txtPassword.Text);

            object result = comm.ExecuteScalar();
            if (result != null)
            {
                //send them where i choose:
                Session["UserLevel"] = result.ToString();
                System.Web.Security.FormsAuthentication.SetAuthCookie(txtUserName.Text, false);
                System.Threading.Thread.Sleep(1000);
                Response.Redirect("Week4.aspx");

                //send them where they tried to go:
                //System.Web.Security.FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, false);
            }
            else
            {
                lblError.Text = "Either your username or password was incorrect";
            }

            conn.Close();
        }

        /// <summary>
        /// Method to insert a new company to database
        /// </summary>
        /// <param name="connection">SQL connection</param>
        /// <param name="row">A row of type System.Data.DataRow - a single company</param>
        /// <param name="message">Error message, if method returns 'false' itself</param>
        /// <returns>Returns 'true' in success, 'false' - in other cases.</returns>
        public static bool Insert(System.Data.SqlClient.SqlConnection connection, ref 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 = "SELECT NEWID() AS NewCompanyID\n" +
                                "  FROM " + Companies.Table;
                cmd.CommandText = sQuery;
                cmd.Connection = connection;
                object res = cmd.ExecuteScalar();
                if (!System.Convert.IsDBNull(res))
                {
                    Guid new_id = (Guid)res;
                    row["CompanyID"] = new_id;

                    sQuery = "INSERT INTO " + Companies.Table + "\n" +
                             "       (CompanyID, ParentID, CompanyName, [Address], WebSite, Phones, Created, Updated)\n" +
                             "VALUES (@CompanyID, @ParentID, @CompanyName, @Address, @WebSite, @Phones, GETDATE(), GETDATE())";
                    cmd.Parameters.AddWithValue("@CompanyID", row["CompanyID"]);
                    cmd.Parameters.AddWithValue("@ParentID", row["ParentID"]);
                    cmd.Parameters.AddWithValue("@CompanyName", row["CompanyName"]);
                    cmd.Parameters.AddWithValue("@Address", row["Address"]);
                    cmd.Parameters.AddWithValue("@WebSite", row["WebSite"]);
                    cmd.Parameters.AddWithValue("@Phones", row["Phones"]);
                    cmd.Connection = connection;
                    cmd.CommandTimeout = 0;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = sQuery;
                    cmd.ExecuteNonQuery();
                    done = true;
                }
                connection.Close();
            }
            catch (System.Exception ex)
            {
                message = ex.Message;
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open) connection.Close();
            }
            return done;
        }

     public static int Save(System.Xml.XmlDocument xd)
     {
         System.Data.SqlClient.SqlConnection conn = ObtainDatabaseConnection();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         cmd.CommandType = System.Data.CommandType.StoredProcedure;
         cmd.Connection = conn;
         conn.Open();
         cmd.CommandText = "usp_saveUserEventDetails";
         cmd.Parameters.AddWithValue("userEventdetails_key", int.Parse(GetVal(xd, "UserEventData")));
         cmd.Parameters.AddWithValue("EventID", GetVal(xd, "EventId"));
         cmd.Parameters.AddWithValue("destinationName", GetVal(xd, "DestinationName"));
         cmd.Parameters.AddWithValue("Long", decimal.Parse(GetVal(xd, "Long")));
         cmd.Parameters.AddWithValue("Lat", decimal.Parse(GetVal(xd, "Lat")));
         cmd.Parameters.AddWithValue("UserDetails_key", int.Parse(GetVal(xd, "UserKey")));
 
         object o = cmd.ExecuteScalar();
         int retVal = int.Parse(o.ToString());
         return retVal;
     }

        public void prueba()
        {
            try
            {

                System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sCnn);
                con.Open();
                System.Data.SqlClient.SqlCommand comando = new System.Data.SqlClient.SqlCommand();
                comando.Connection = con;
                comando.CommandText = "SELECT count (*) FROM dbo.materias";
                int Cant = Convert.ToInt32(comando.ExecuteScalar());
                con.Close();
                Console.WriteLine("Cantidad de registros: " + Cant.ToString());
            }
            catch(Exception e)
            {
               Console.WriteLine( e.StackTrace);

            }
        }

        public static int Save(System.Xml.XmlDocument xd)
        {
            System.Data.SqlClient.SqlConnection conn = ObtainDatabaseConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "usp_saveUserDetails";
            cmd.Parameters.AddWithValue("userdetails_key", int.Parse(GetVal(xd, "UserDetailsKey")));
            cmd.Parameters.AddWithValue("yahoousername", GetVal(xd,"YahooUsername"));
            cmd.Parameters.AddWithValue("email", GetVal(xd, "Email"));
            cmd.Parameters.AddWithValue("password", GetVal(xd, "Password"));
            object o = cmd.ExecuteScalar();
            int retVal = int.Parse(o.ToString());
            return retVal;


            

        }

        public double ProcessCheck(double value)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HuberTrackerConnection"].ToString();
            conn.ConnectionString = connectionstring;
            conn.Open();
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;

            System.Data.SqlClient.SqlTransaction trans;
            trans = conn.BeginTransaction();
            comm.Transaction = trans;

            string sql = "update huber_accounts set balance = balance - @balance where accountid = 1";

            comm.CommandText = sql;
            comm.Parameters.AddWithValue("@balance", value);

            comm.ExecuteNonQuery();

            sql = "select balance from huber_accounts where accountid = 1";

            comm.Parameters.Clear();
            comm.CommandText = sql;
            object result = comm.ExecuteScalar();

            if (Convert.ToDouble(result) < .01)
            {
                trans.Rollback();
                conn.Close();

            }
            else
            {
                trans.Commit();
                conn.Close();

            }
            return Convert.ToDouble(result);
        }

        private void btnProcess_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection connection =
                new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TransactionProcessingTest.Properties.Settings.devrystudentsp10ConnectionString"].ConnectionString);
            connection.Open();
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            command.Connection = connection;
            System.Data.SqlClient.SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead);
            command.Transaction = transaction;
            string sql = "";
            bool isGood = true;
            for (int i = 0; i < int.Parse(txtTimes.Text); i++)
            {
                sql = "update huber_bankcustomers set balance = balance - @balance where customerid = @customerid";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@balance",int.Parse(txtAmount.Text));
                command.Parameters.AddWithValue("@customerid",txtCustomerId.Text);
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "select balance from huber_bankcustomers where customerid = @customerid";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@customerid", txtCustomerId.Text);
                command.CommandText = sql;
                if (int.Parse(command.ExecuteScalar().ToString()) < 0)
                {
                    transaction.Rollback();
                    isGood = false;
                    i = int.Parse(txtTimes.Text);
                    MessageBox.Show("Sorry that transaction was rolled back. The customer is not allowed to go below a 0 balance.");
                }

            }
            if (isGood)
            {
                transaction.Commit();
            }
            LoadGrid();
        }

        protected Boolean bHasOpenSlots(int SID, DateTime Current)
        {
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["TL_Connection_String"].ToString());
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("IF((SELECT COUNT(BRLStudentNID) AS TotalCount FROM tlBRLStudentAppointments WHERE (tlBRLStudentAppointments.BRLSessionDateID = @ID) AND (BRLStudentAppointmentTimeRequested = @DTParam)) < (SELECT BRLSessionMaxSeats FROM dbo.tlBRLSessionDates WHERE BRLSessionDateID = @ID)) BEGIN SELECT 1 END ELSE BEGIN SELECT 0 END", connection);
            System.Data.SqlClient.SqlParameter safeID = new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.VarChar);
            System.Data.SqlClient.SqlParameter safeDT = new System.Data.SqlClient.SqlParameter("@DTParam", System.Data.SqlDbType.DateTime);

            safeID.Value = SID;
            command.Parameters.Add(safeID);

            safeDT.Value = Current.ToString();
            command.Parameters.Add(safeDT);

            //Open The Connection
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }

            try
            {
                String i = command.ExecuteScalar().ToString();
                connection.Close();
                return (int.Parse(i) == 1 ? true : false);
            }
            catch (Exception ex)
            {
                connection.Close();
                ErrorLabel.Text = "Database error, try again.</br>" + ex.ToString();
                ErrorPanel.Visible = true;

                ScanPanel.Visible = true;
                SchedulePanel.Visible = false;
                DateSelectionPanel.Visible = false;
                return false;
            }
        }

 private void btnOk_Click(object sender, EventArgs e)
 {
     // checking if the inboxs are empty
     if (txbUserName.Text != "" & txbPassWord.Text != "") {
         //sql query
         string queryText = "SELECT Count(*) FROM Users " +
                            "WHERE username = @Username AND password = @Password";
         // starting the sql connection
         using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("Server=.\\SQLEXPRESS;Database=Thesus;Intergrted Security=true"))
         // passing the sql query
         using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(queryText, cn)) {
             cn.Open();
             cmd.Parameters.AddWithValue("@Username", txbUserName.Text);
             cmd.Parameters.AddWithValue("@Password", txbPassWord.Text);
             int result = (int)cmd.ExecuteScalar();
             // checking how many results are returned
             if (result > 0){
                 MessageBox.Show("loggin in!");
             } else {
                 MessageBox.Show("User Not Found!");
             }
         }
     }
 }