Пример #1
0
        /// <summary>
        /// 数据库查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>结果集</returns>
        public static DataTable Select(string sql)
        {
            MySqlConnection Conn = dbConn;

            DataTable        dtResult = null;
            MySqlCommand     Cmd      = null;
            MySqlDataAdapter Adapt    = null;
            DataSet          Ds       = null;

            for (int i = 0; i <= ConnTimes; i++)
            {
                try
                {
                    Cmd             = new MySqlCommand(sql, Conn);
                    Cmd.CommandType = CommandType.Text;
                    Adapt           = new MySqlDataAdapter(Cmd);
                    Ds = new DataSet();
                    Adapt.Fill(Ds);
                    dtResult = Ds.Tables[0];
                    break;
                }
                catch (MySqlException e)
                {
                    string LogCont = "错误信息:" + e.Message.Trim() + ",错误语句:" + sql + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);
                    if (MessageBox.Show("当前数据库连接失败,您是否尝试重新连接?\r\n    点击“是”继续连接;点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        Environment.Exit(0);
                    }
                    else
                    {
                        if (i >= ConnTimes)
                        {
                            MessageBox.Show("已经超过最大连接次数,点击确定后退出!");
                            Environment.Exit(0);
                        }
                        else
                        {
                            Connect();
                        }
                    }
                }
                finally
                {
                    if (Cmd != null)
                    {
                        Cmd.Dispose();
                    }
                    if (Adapt != null)
                    {
                        Adapt.Dispose();
                    }
                    if (Ds != null)
                    {
                        Ds.Dispose();
                    }
                }
            }
            return(dtResult);
        }
Пример #2
0
        public static DataTable Select(string sql)
        {
            OleDbConnection conn = dbConn;

            DataTable        dtResult = null;
            OleDbDataAdapter adapt    = null;
            DataSet          ds       = null;

            for (int i = 0; i <= ConnTimes; i++)
            {
                try
                {
                    adapt = new OleDbDataAdapter(sql, conn);
                    ds    = new DataSet();
                    adapt.Fill(ds);
                    dtResult = ds.Tables[0];
                    break;
                }
                catch (SqlException e)
                {
                    string LogCont = "错误信息:" + e.Message.Trim() + ",错误语句:" + sql + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);
                    if (MessageBox.Show("当前数据库连接失败,您是否尝试重新连接?\r\n    点击“是”继续连接;点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        Environment.Exit(0);
                    }
                    else
                    {
                        if (i >= ConnTimes)
                        {
                            MessageBox.Show("已经超过最大连接次数,点击确定后退出!");
                            Environment.Exit(0);
                        }
                        else
                        {
                            Database.Connect();
                        }
                    }
                }
                finally
                {
                    if (adapt != null)
                    {
                        adapt.Dispose();
                    }
                    if (ds != null)
                    {
                        ds.Dispose();
                    }
                }
            }
            return(dtResult);
        }
Пример #3
0
        public static void ExecuteProc(string sql, params MySqlParameter[] cmdParms)
        {
            MySqlConnection Conn    = dbConn;
            int             iResult = -1;
            MySqlCommand    Cmd     = null;

            for (int i = 0; i <= ConnTimes; i++)
            {
                try
                {
                    Cmd = new MySqlCommand(sql, Conn);
                    if (cmdParms != null && cmdParms.Length > 0)
                    {
                        Cmd.Parameters.AddRange(cmdParms);
                    }

                    Cmd.CommandType = CommandType.StoredProcedure;

                    iResult = Cmd.ExecuteNonQuery();
                    Cmd.Parameters.Clear();
                    break;
                }
                catch (MySqlException e)
                {
                    string LogCont = "【错误】:" + "存储过程:" + sql + ",错误信息:" + e.Message.Trim() + ",错误语句:" + sql + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);
                    if (MessageBox.Show("当前数据库连接失败,您是否尝试重新连接?\r\n    点击“是”继续连接;点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        Environment.Exit(0);
                    }
                    else
                    {
                        if (i >= ConnTimes)
                        {
                            MessageBox.Show("已经超过最大连接次数,点击确定后退出!");
                            Environment.Exit(0);
                        }
                        else
                        {
                            Connect();
                        }
                    }
                }
                finally
                {
                    if (Cmd != null)
                    {
                        Cmd.Dispose();
                    }
                }
            }
        }
Пример #4
0
        //批量插入语句
        //MySQL
        public static int BatchInsertSql(string insertSql, List <string> values, int batchSize = 500)
        {
            int iResult = 0;

            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                conn.Open();
                if (conn.State != ConnectionState.Open)
                {
                    return(-1);
                }
                string       sql = "";
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = null;

                //计算批量提交次数
                int commitCount = 1;
                if (values.Count % batchSize > 0)
                {
                    commitCount = values.Count / batchSize + 1;
                }
                else
                {
                    commitCount = values.Count / batchSize;
                }

                try
                {
                    for (int i = 0; i < commitCount; i++)
                    {
                        tx = conn.BeginTransaction();
                        cmd.Transaction = tx;

                        sql = GetBatchInsertSql(insertSql, values, i * batchSize, batchSize);

                        cmd.CommandText = sql;
                        iResult        += cmd.ExecuteNonQuery();
                        tx.Commit();
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    tx.Rollback();
                    string LogCont = "错误信息:" + e.Message.Trim() + ",错误语句:" + cmd.CommandText + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);
                }
            }

            return(iResult);
        }
Пример #5
0
        /// <summary>
        /// 更新数据库操作,包括删除、修改与添加
        /// </summary>
        public static int ExecuteSQL(string sql, string modelForm = "")
        {
            MySqlConnection Conn    = dbConn;
            int             iResult = -1;
            MySqlCommand    Cmd     = null;

            for (int i = 0; i <= ConnTimes; i++)
            {
                try
                {
                    Cmd             = new MySqlCommand(sql, Conn);
                    Cmd.CommandType = CommandType.Text;

                    iResult = Cmd.ExecuteNonQuery();

                    string LogCont = "【正常】:" + "模块名称:" + modelForm + ",执行语句:" + sql + ",执行时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);

                    break;
                }
                catch (MySqlException e)
                {
                    string LogCont = "【错误】:" + "模块名称:" + modelForm + ",错误信息:" + e.Message.Trim() + ",错误语句:" + sql + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                    DatabaseLog.WriteLog(LogCont);
                    if (MessageBox.Show("当前数据库连接失败,您是否尝试重新连接?\r\n    点击“是”继续连接;点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        Environment.Exit(0);
                    }
                    else
                    {
                        if (i >= ConnTimes)
                        {
                            MessageBox.Show("已经超过最大连接次数,点击确定后退出!");
                            Environment.Exit(0);
                        }
                        else
                        {
                            Connect();
                        }
                    }
                }
                finally
                {
                    if (Cmd != null)
                    {
                        Cmd.Dispose();
                    }
                }
            }
            return(iResult);
        }
Пример #6
0
        public static bool Connect()
        {
            if (dbConn == null)
            {
                dbConn = new MySqlConnection();
            }

            string strCon = String.Format("Database='{0}';Data Source='{1}'; Port = '{2}';User Id='{3}';Password='******';charset='{5}';pooling=true ",//;Persist Security Info=True
                                          DbName,
                                          Host,
                                          Port,
                                          User,
                                          Psw,
                                          CharSet);

            MySqlConnection conn = dbConn;

            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.ConnectionString = strCon;
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (MySqlException e)
            {
                string LogCont = "错误信息:" + e.Message.Trim() + ",错误时间:" + System.DateTime.Now.ToString().Trim() + "\r\n";
                DatabaseLog.WriteLog(LogCont);
                return(false);
            }
        }