C# SqlClient.SqlConnection类代码示例

 private void InitClass(int id, int groupID)
 {
     this.RoleID = id;
     this.GroupID = groupID;
     using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[Configuration.ConnectionStringName].ConnectionString))
     {
         using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Authorization_RoleToControlGroup WHERE [email protected] AND [email protected]", conn))
         {
             try
             {
                 conn.Open();
                 cmd.Parameters.AddWithValue("@role", id);
                 cmd.Parameters.AddWithValue("@group", groupID);
                 System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
                 while (reader.Read())
                     this.id = reader.GetInt32(0);
             }
             catch (Exception ex)
             {
                 if (Configuration.s_log != null)
                     Configuration.s_log.Error("[Ошибка модуля авторизации] [Инициализация класса роли группы элементов управления] ", ex);
             }
         }
     }
 }

 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 void Persist(IEnumerable<TradeRecord> trades)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=TradeDatabase;Integrated Security=True;"))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    foreach (var trade in trades)
                    {
                        var command = connection.CreateCommand();
                        command.Transaction = transaction;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "dbo.insert_trade";
                        command.Parameters.AddWithValue("@sourceCurrency", trade.SourceCurrency);
                        command.Parameters.AddWithValue("@destinationCurrency", trade.DestinationCurrency);
                        command.Parameters.AddWithValue("@lots", trade.Lots);
                        command.Parameters.AddWithValue("@price", trade.Price);

                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
                connection.Close();
            }

            logger.LogInfo("{0} trades processed", trades.Count());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objInvestorLog"></param>
        /// <returns></returns>
        internal int AddNewInvestorLog(Business.InvestorLog objInvestorLog)
        {
            int Result = -1;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.InvestorLogTableAdapter adap = new DSTableAdapters.InvestorLogTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;
                Result = int.Parse(adap.AddNewInvestorLog(objInvestorLog.InvestorInstance.InvestorID, objInvestorLog.Time, objInvestorLog.IP, objInvestorLog.Message,
                                    objInvestorLog.Status).ToString());
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

 public static string ChangePassword(string userName, string password, string newPassword)
 {
     string encryptedPassword = Encrypt(password);
     string encryptedNewPassword = Encrypt(newPassword);
     string success = "nogo";
     try
     {
         using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MasonMaster"].ConnectionString))
         {
             connection.Open();
             using (var command = connection.CreateCommand())
             {
                 command.CommandText = string.Format("Update UserProfile set password = '{2}' where UserName='{0}' and Password='{1}'",
                     userName, encryptedPassword, encryptedNewPassword);
                 if (command.ExecuteNonQuery() == 1)
                     success = "ok";
             }
         }
     }
     catch (Exception ex)
     {
         success = ex.Message;
     }
     return success;
 }

        public static int GetUserId(System.Web.HttpContext HttpContext)
        {
            int result = 0;
            try
            {
                
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
                {
                    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT employee_id FROM employee WHERE login LIKE @name", conn))
                    {
                        try
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@name", HttpContext.User.Identity.Name.Remove(0,3));
                            System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                                result = reader.GetInt32(0);
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                AuthLib.Core.Logging.WriteEntry("Ошибка при получении идентификатора пользователя", ex);
                throw new Exception("Ошибка при получении идентификатора пользователя", ex);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="AgentGroupID"></param>
        /// <returns></returns>
        internal Business.AgentGroup GetAgentGroupByAgentGroupID(int AgentGroupID)
        {
            Business.AgentGroup Result = new Business.AgentGroup();
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.AgentGroupTableAdapter adap = new DSTableAdapters.AgentGroupTableAdapter();
            DS.AgentGroupDataTable tbAgentGroup = new DS.AgentGroupDataTable();

            try
            {
                conn.Open();
                adap.Connection = conn;
                tbAgentGroup = adap.GetAgentGroupByAgentGroupID(AgentGroupID);

                if (tbAgentGroup != null)
                {
                    Result.AgentGroupID = tbAgentGroup[0].AgentGroupID;
                    Result.Name = tbAgentGroup[0].Name;
                    Result.Comment = tbAgentGroup[0].Comment;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

        public string Create(Customer customerEntity)
        {
            try
            {
                using(System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
                {
                    sqlConnection.Open();
                    string sqlQuery =
                        "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName, @LastName, @Address, @City)";
                    sqlConnection.Execute(sqlQuery,
                                          new
                                              {
                                                  customerEntity.FirstName,
                                                  customerEntity.LastName,
                                                  customerEntity.Address,
                                                  customerEntity.City,
                                              });
                    sqlConnection.Close();
                }
                return "Created";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        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();
            }
        }

        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);
            }
        }

        public static void Main(string[] args)
        {
            try
            {
                using (var connection = new System.Data.SqlClient.SqlConnection(ConnectionString))
                {
                    connection.Open();

                    var sql = "select * FROM People";

                    var people = connection.Query<People>(sql);

                    foreach (var person in people)
                    {
                        Console.WriteLine(JsonConvert.SerializeObject(person));
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }

            Console.ReadLine();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="values"></param>
        /// <returns></returns>
        internal int AddStatement(List<Business.Statement> values)
        {
            int result = -1;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.StatementTableAdapter adap = new DSTableAdapters.StatementTableAdapter();

            try
            {
                if (values != null && values.Count > 0)
                {
                    conn.Open();
                    adap.Connection = conn;

                    int count = values.Count;
                    for (int i = 0; i < count; i++)
                    {
                        result = int.Parse(adap.AddNewStatement(values[i].InvestorCode, values[i].Content, values[i].TimeStatement,
                            values[i].Email, values[i].StatementType).ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return result;
        }

 private void CbBackups_DropDown_1(object sender, EventArgs e)
 {
     using (var connection = new System.Data.SqlClient.SqlConnection(string.Format("Server={0};Database={1};Trusted_Connection=True;", TxtLocalBkp.Text, TxtDataBase.Text)))
     {
         connection.Open();
         using (var command = new System.Data.SqlClient.SqlCommand(
             "SELECT physical_device_name FROM msdb.dbo.backupmediafamily " +
             "INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id " +
             "WHERE (msdb.dbo.backupset.database_name LIKE @DatabaseName)", connection))
         {
             command.Parameters.AddWithValue("DatabaseName", TxtDataBase.Text);
             using (var reader = command.ExecuteReader())
             {
                 var table = new DataTable();
                 table.Load(reader);
                 table.Columns.Add("FriendlyName");
                 foreach (DataRow row in table.Rows)
                 {
                     row["FriendlyName"] = System.IO.Path.GetFileName(row["physical_device_name"].ToString());
                 }
                 if (CbBackups.DataSource != null && CbBackups.DataSource is DataTable)
                 {
                     var oldTable = ((DataTable)CbBackups.DataSource);
                     CbBackups.DataSource = null;
                     oldTable.Dispose();
                 }
                 CbBackups.DataSource = table;
                 CbBackups.DisplayMember = "FriendlyName";
                 CbBackups.ValueMember = "physical_device_name";
             }
         }
     }
 }

        public static int ExecuteNonQuery(string strSQL)
        {
            int retVal = 0;

            try
            {

                using (System.Data.Common.DbConnection con = new System.Data.SqlClient.SqlConnection(GetConnectionString()))
                {
                    using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = strSQL;

                        if (cmd.Connection.State != System.Data.ConnectionState.Open)
                            cmd.Connection.Open();

                        retVal = cmd.ExecuteNonQuery();

                        if (cmd.Connection.State != System.Data.ConnectionState.Closed)
                            cmd.Connection.Close();
                    } // End Using cmd
                }
            }
            catch (System.Exception ex)
            {
                // System.Console.WriteLine(ex.Message);
                retVal = -1;
            }

            return retVal;
        }

       public static bool AddGroup(string intranetGroupName)
        {
            try
            {
                int groupID = 0;
                //Получаем идентификатор группы
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
                {
                    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO groups(group_id,group_name) VALUES((SELECT MIN(group_id)-1 FROM groups WHERE id>-1000), @name)", conn))
                    {
                        try
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@name", intranetGroupName);
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Core.Logging.WriteEntry("Ошибка добавления группы.", ex, 1);
                return false;
            }
        }