C# DBConnection类代码示例

        /// <summary>
        /// Executes this command against the specified database connection and returns the auto-increment value of the inserted row, or null if it is not an
        /// auto-increment table.
        /// </summary>
        public object Execute( DBConnection cn )
        {
            var cmd = cn.DatabaseInfo.CreateCommand();
            cmd.CommandText = "INSERT INTO " + table;
            if( columnModifications.Count == 0 )
                cmd.CommandText += " DEFAULT VALUES";
            else {
                cmd.CommandText += "( ";
                foreach( var columnMod in columnModifications )
                    cmd.CommandText += columnMod.ColumnName + ", ";
                cmd.CommandText = cmd.CommandText.Substring( 0, cmd.CommandText.Length - 2 );
                cmd.CommandText += " ) VALUES( ";
                foreach( var columnMod in columnModifications ) {
                    var parameter = columnMod.GetParameter();
                    cmd.CommandText += parameter.GetNameForCommandText( cn.DatabaseInfo ) + ", ";
                    cmd.Parameters.Add( parameter.GetAdoDotNetParameter( cn.DatabaseInfo ) );
                }
                cmd.CommandText = cmd.CommandText.Substring( 0, cmd.CommandText.Length - 2 );
                cmd.CommandText += " )";
            }
            cn.ExecuteNonQueryCommand( cmd );

            if( !cn.DatabaseInfo.LastAutoIncrementValueExpression.Any() )
                return null;
            var autoIncrementRetriever = cn.DatabaseInfo.CreateCommand();
            autoIncrementRetriever.CommandText = "SELECT {0}".FormatWith( cn.DatabaseInfo.LastAutoIncrementValueExpression );
            var autoIncrementValue = cn.ExecuteScalarCommand( autoIncrementRetriever );
            return autoIncrementValue != DBNull.Value ? autoIncrementValue : null;
        }

 private bool CheckEEP_ExecutingServiceStateTable(string sProjectID)
 {
     bool blExistence = false;
     DBConnection cDBConnection;
     try
     {
         cDBConnection = new DBConnection();
         DataTable dtt = new DataTable();
         DataRow[] dtwSelection;
         string cmdSQL = "select name from SysObjects order by name";
         dtt = cDBConnection.execDataTable(cmdSQL, gsEEPDB);
         dtwSelection = dtt.Select("name = 'EEP_ExecutingServiceState'");
         switch (dtwSelection.Length)
         {
             case 1: // have being the table.
                 blExistence = true;
                 gLogger.Trace("已經有EEP_ExecutingServiceState的Table");
                 break;
             case 0://Create a new table
                 cmdSQL = "CREATE TABLE dbo.EEP_ExecutingServiceState (ProjectID NVARCHAR (50) NOT NULL, ServiceName NVARCHAR (50) NOT NULL, RecTime DATETIME NOT NULL, AlarmIntervalMinute INT NOT NULL, CONSTRAINT PK_EEP_ExecutingServiceState PRIMARY KEY (ProjectID, ServiceName));";
                 //cmdSQL = string.Format("CREATE TABLE [dbo].[RMC_HistoryData_{0}_{1}]([EquipmentID] [nvarchar](50) NULL, [PointID] [nvarchar](50) NULL, [PointValue] [nvarchar](50) NULL, [RecTime] [datetime] NULL) ON [PRIMARY]", sProjectID, DateTime.Now.ToString("yyyyMM"));
                 cDBConnection.execNonQuery(cmdSQL, gsEEPDB);
                 blExistence = true;
                 gLogger.Trace("產生新的EEP_ExecutingServiceState的Table");
                 break;
         }
     }
     catch (Exception ex)
     {
         gLogger.ErrorException("UpdateServiceTime.CheckEEP_ExecutingServiceStateTable", ex);
     }
     return blExistence;
 }

        public Company Get(int id, string lang)
        {
            DBConnection dbConnection = new DBConnection(lang);
            company = dbConnection.GetCompanyById(id);
            //company.licenceList = new List<Licence>();

            //if (company != null && company.company_id > 0)
            //{
            //    var licenceList = dbConnection.GetAllLicenceByCompanyId(company.company_id, "active");
            //    if (licenceList != null && licenceList.Count > 0)
            //    {
            //       company.licenceList = licenceList;

            //        foreach (var licence in company.licenceList)
            //        {
            //            licence.deviceList = new List<Device>();
            //            //Get Device
            //            var deviceList = dbConnection.GetAllDevice("", "", licence.original_licence_no);
            //            if (deviceList != null && deviceList.Count > 0)
            //            {
            //                licence.deviceList = deviceList;
            //            }
            //        }
            //    }
            //}
            return company;
        }

        private string gsProjectID = ConfigurationManager.AppSettings.Get("ProjectID"); //從AppConfig讀取「ProjectID」參數

        #endregion Fields

        #region Methods

        /// <summary>
        /// 紀錄WindowServic運作時間
        /// 用來判斷WindowService是否有正常作動
        /// </summary>
        public void Update_Time()
        {
            DBConnection cDBConnection;
            bool blFlag;
            string cmdSQL = string.Empty;
            string sServiceName = gsProjectID + ".ModbusTCP.Adapter(" + gsIP + ":" + gsPort + ")";
            try
            {
                cDBConnection = new DBConnection();
                blFlag = false;
                blFlag = CheckEEP_ExecutingServiceStateTable(gsProjectID);
                if (blFlag == false)
                {
                    gLogger.Error("產生EEP_ExecutingServiceState Table失敗");
                }
                cmdSQL = string.Format("SELECT * FROM EEP_ExecutingServiceState WHERE ProjectID = '{0}' AND ServiceName = '{1}'", gsProjectID, sServiceName);
                DataTable dttExecutingServiceState = cDBConnection.execDataTable(cmdSQL, gsEEPDB);
                if (dttExecutingServiceState.Rows.Count > 0)
                {
                    cmdSQL = string.Format("UPDATE EEP_ExecutingServiceState SET RecTime = '{2}', AlarmIntervalSecond = '{3}' WHERE ProjectID = '{0}' AND ServiceName = '{1}'", gsProjectID, sServiceName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), dttExecutingServiceState.Rows[0]["AlarmIntervalSecond"].ToString());
                    cDBConnection.execNonQuery(cmdSQL, gsEEPDB);
                    gLogger.Trace("更新WindowService監控時間成功");
                }
                else
                {
                    cmdSQL = string.Format("INSERT INTO EEP_ExecutingServiceState (ProjectID, ServiceName, RecTime, AlarmIntervalSecond) VALUES ('{0}', '{1}', '{2}', '{3}')", gsProjectID, sServiceName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), giSystemRunTimeRecord);
                    cDBConnection.execNonQuery(cmdSQL, gsEEPDB);
                }
            }
            catch (Exception ex)
            {
                gLogger.ErrorException("UpdateServiceTime.Update_Time", ex);
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            DBConnection bindComboBox = new DBConnection();
            City cities = new City();
            DataTable DT = new DataTable();
            DT = bindComboBox.BindDropdown(cities.SearchCities(), "City_Name", "City_Number");
            CityDDL.DataSource = DT;
            CityDDL.DataValueField = "City_Number";
            CityDDL.DataTextField = "City_Name";

            CityDDL.DataBind();

            GenderDDL.Items.Add("Male");
            GenderDDL.Items.Add("Female");

            //EmployeeType
            EmployeeType empType = new EmployeeType();
            DT = bindComboBox.BindDropdown(empType.SearchEmployeeTypes(), "Employee_Type_Name", "Employee_Type_Number");

            EmployeeTypeDDL.DataSource = DT;
            EmployeeTypeDDL.DataValueField = "Employee_Type_Name";
            EmployeeTypeDDL.DataTextField = "Employee_Type_Number";

            EmployeeTypeDDL.DataBind();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Get database connection information from DBConnection.cs
            DBConnection conString = new DBConnection();
            string dbConnString;
            conString.MyProperty = "";
            dbConnString = conString.MyProperty;

            //Check all fields from SafeInsert.cs
            SafeInsert safeInsert = new SafeInsert();
            string phoneIn = safeInsert.cityFix(phoneNumberIn.Text);

            try
            {
                MySqlConnection connection = new MySqlConnection(dbConnString);
                connection.Open(); //open connection

                MySqlDataAdapter MyDa = new MySqlDataAdapter();
                string SqlSelectAll = "Select * from customerdetails;";
                MyDa.SelectCommand = new MySqlCommand(SqlSelectAll, connection);

                DataTable table = new DataTable(); //Setup Bindings to attach to DataGrid
                MyDa.Fill(table);
                BindingSource bSource = new BindingSource();
                bSource.DataSource = table;

                //Fill grid
                dgView.DataSource = bSource;
            }

            catch (Exception)
            {
                MessageBox.Show("Error Connecting");
            }
        }

        public void FillSession(string usuario)
        {
            DBConnection dbconnectionObj = new DBConnection();
            string query = @"select
            u.id as usuario_id,
            u.usuario as usuario,
            p.id as empleado_id,
            p.num_placa,
            p.nombre as nombre_personal,
            p.delegacion_id
            from
            usuarios as u inner join personal as p on u.empleado_id = p.id
            where u.usuario = @usuario";

            DataTable dt = dbconnectionObj.Select(query,
                new SqlParameter("@usuario", usuario)
                );
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Sesion.usuario_id = int.Parse(row["usuario_id"].ToString());
                    Sesion.empleado_id = int.Parse(row["empleado_id"].ToString());
                    Sesion.nombre_personal = row["nombre_personal"].ToString();
                    Sesion.num_placa = row["num_placa"].ToString();
                    Sesion.delegacion_id = int.Parse(row["delegacion_id"].ToString());
                }
            }
        }

 public List<Genre> GetAllGenres()
 {
     using (var db = new DBConnection())
     {
         return db.Genres.ToList();
     }
 }

        internal static void Generate(
            DBConnection cn, TextWriter writer, string baseNamespace, Database database, Configuration.SystemDevelopment.Database configuration)
        {
            if( configuration.queries == null )
                return;

            info = cn.DatabaseInfo;
            writer.WriteLine( "namespace " + baseNamespace + "." + database.SecondaryDatabaseName + "Retrieval {" );

            foreach( var query in configuration.queries ) {
                List<Column> columns;
                try {
                    columns = validateQueryAndGetColumns( cn, query );
                }
                catch( Exception e ) {
                    throw new UserCorrectableException( "Column retrieval failed for the " + query.name + " query.", e );
                }

                CodeGenerationStatics.AddSummaryDocComment( writer, "This object holds the values returned from a " + query.name + " query." );
                writer.WriteLine( "public static partial class " + query.name + "Retrieval {" );

                // Write nested classes.
                DataAccessStatics.WriteRowClasses( writer, columns, localWriter => { } );
                writeCacheClass( writer, database, query );

                writer.WriteLine( "private const string selectFromClause = @\"" + query.selectFromClause + " \";" );
                foreach( var postSelectFromClause in query.postSelectFromClauses )
                    writeQueryMethod( writer, database, query, postSelectFromClause );
                writer.WriteLine( "static partial void updateSingleRowCaches( Row row );" );
                writer.WriteLine( "}" ); // class
            }
            writer.WriteLine( "}" ); // namespace
        }

 public List<String> getListOfForums()
 {
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = "SELECT f_title FROM [projectdb1].[dbo].[Forum]";
     return myConnection.getResultsList(query, "f_title");
 }

 public Genre GetGenreById(int? id)
 {
     using (var db = new DBConnection())
     {
         return db.Genres.FirstOrDefault(x => x.id == id);
     }
 }

 public frmEditFunctionSettings(SpecialFunction _function, DBConnection _dbCon, string _tableName)
 {
     InitializeComponent();
     function = _function;
     dbCon = _dbCon;
     tableName = _tableName;
 }

 public Artist GetArtistById(int? id)
 {
     using (var db = new DBConnection())
     {
         return db.Artists.FirstOrDefault(x => x.id == id);
     }
 }

 public void UpdateArtist(Artist artist)
 {
     using (var db = new DBConnection())
     {
         db.Entry(artist).State = EntityState.Modified;
         db.SaveChanges();
     }
 }

 private void btnCreateConnection_Click(object sender, EventArgs e)
 {
     DBConnection con = new DBConnection((string)cboxServ.SelectedItem, (string)cboxDatabase.SelectedItem, txtUser.Text, txtPass.Text);
     frmManagerMain main = new frmManagerMain(con);
     this.Hide();
     main.ShowDialog();
     this.Show();
 }