コード例 #1
0
        public static List <string> GetFirstRow(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
                                                params DbParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(connString))
            {
                throw new ArgumentNullException("connString");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                var conn = DBFactory.NewConnection(dbType, connString);
                conn.Open();

                return(GetFirstRow(conn, dbType, cmdType, cmdText, commandParameters));
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.GetFirstRow 【" + cmdText + "】", ex);
                OpenConnection(dbType, connString);  //重新刷新
                return(null);
            }
        }
コード例 #2
0
        public static DbConnection NewConnection(EmDbType dbtype, string connectionString)
        {
            if (String.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException("connectionString");
            }

            switch (dbtype)
            {
            case EmDbType.SqlServer:
                return(new SqlConnection(connectionString));

            case EmDbType.Oracle:
                return(new OracleConnection(connectionString));

            case EmDbType.MySql:
                return(new MySqlConnection(connectionString));

            case EmDbType.Access:
                return(new OleDbConnection(connectionString));

            default:
                throw new ArgumentException("不支持的数据库类型", dbtype.ToString());
            }
        }
コード例 #3
0
        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="connString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static string ExecuteNonQuery(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
                                             params DbParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(connString))
            {
                throw new ArgumentNullException("connString");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                DbConnection conn = DBFactory.NewConnection(dbType, connString);

                return(ExecuteNonQuery(conn, dbType, cmdType, cmdText, commandParameters));
            }
            catch (Exception ex)
            {
                EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex);
                OpenConnection(dbType, connString);  //重新连接
                return(ex.Message);
            }
        }
コード例 #4
0
        /// <summary>
        /// SQL语句的参数类型转换
        /// </summary>
        /// <param name="parameterNames"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public static DbParameter[] ChangeToDbParams(EmDbType dbtype, string[] parameterNames, string[] parameterValues)
        {
            List <DbParameter> parameterItems = new List <DbParameter>();

            if ((parameterNames == null) || (parameterValues == null))
            {
                return(parameterItems.ToArray());
            }
            for (int i = 0; i < parameterNames.Length; i++)
            {
                DbParameter dbParam = null;
                switch (dbtype)
                {
                case EmDbType.SqlServer:
                    dbParam = new SqlParameter(parameterNames[i], parameterValues[i]);
                    break;

                case EmDbType.Oracle:
                    dbParam = new OracleParameter(parameterNames[i], parameterValues[i]);
                    break;

                case EmDbType.MySql:
                    dbParam = new MySqlParameter(parameterNames[i], parameterValues[i]);
                    break;

                case EmDbType.Access:
                    dbParam = new OleDbParameter(parameterNames[i], parameterValues[i]);
                    break;
                }
                parameterItems.Add(dbParam);
            }
            return(parameterItems.ToArray());
        }
コード例 #5
0
        public static List <string> GetFirstRow(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
                                                params DbParameter[] commandParameters)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("conn");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                List <string> result = new List <string>();
                DataSet       ds     = GetDataSet(conn, dbType, cmdType, cmdText, commandParameters);
                if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        result.Add(dr[i].ToString());
                    }
                }

                return(result);
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.GetFirstRow 【" + cmdText + "】", ex);
                OpenConnection(dbType, conn);  //重新连接
                return(null);
            }
        }
コード例 #6
0
        public static EmDbType MainDbType = EmDbType.None; //主 数据库类型

        #endregion

        #region 0.1 主数据初始化

        /// <summary>
        /// 初始化主连接
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <param name="datasource">数据源</param>
        /// <param name="database">数据库</param>
        /// <param name="userid">用户名</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public static bool InitialMainConn(EmDbType dbType, string datasource, string database, string userid, string password)
        {
            string connnString = DBFactory.BuildConnStr(dbType, datasource, database, userid, password);

            MainConnectionString = connnString;
            MainDbType           = dbType;
            MainConn             = DBFactory.NewConnection(dbType, connnString);

            return(InitialMainConn(dbType, connnString));
        }
コード例 #7
0
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="serverPKID">ID</param>
        /// <param name="address">地址:格式 IP地址;登录名;密码;数据库;数据库类型</param>
        /// <param name="updateRate"></param>
        /// <param name="deviceTagParams"></param>
        /// <param name="callback"></param>
        private void Initial(Int64 serverPKID, string address, int updateRate,
                             List <DeviceTagParam> deviceTagParams, DataChangeEventHandler callback)
        {
            pkid = serverPKID;
            string[] connStrings = address.Split('|');  //分隔符

            #region 初始化参数

            DbType           = EmDbType.SqlServer;
            ConnectionString = address;

            #region 数据库类型及连接语句

            if (connStrings.Count() >= 2)
            {
                ConnectionString = connStrings[1];

                string sdbtype = connStrings[0].ToLower();
                if (sdbtype == "access")
                {
                    DbType = EmDbType.Access;
                }
                if (sdbtype == "oracle")
                {
                    DbType = EmDbType.Oracle;
                }
                if (sdbtype == "mysql")
                {
                    DbType = EmDbType.MySql;
                }
            }

            if (connStrings.Count() >= 5) //分开的数据库连接数据
            {
                if (!string.IsNullOrEmpty(connStrings[2]) && !string.IsNullOrEmpty(connStrings[3]) &&
                    !string.IsNullOrEmpty(connStrings[4]))
                {
                    ConnectionString = DBFactory.BuildConnStr(DbType, connStrings[1], connStrings[2], connStrings[3],
                                                              connStrings[4]);
                }
            }

            #endregion

            MainConn = DBFactory.NewConnection(DbType, ConnectionString); //连接语句

            Callback = callback;                                          //设置回调函数

            DeviceTags = deviceTagParams;                                 //标签

            #endregion

            CustomProtocol   = (connStrings.Length >= 6) ? connStrings[5] : ""; //自定义协议
            ProtocolVariable = (connStrings.Length >= 7) ? connStrings[6] : ""; //自定义协议参数
        }
コード例 #8
0
        /// <summary>
        /// 初始化主连接
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <param name="connnString">连接字符串</param>
        /// <returns></returns>
        public static bool InitialMainConn(EmDbType dbType, string connnString)
        {
            if (string.IsNullOrEmpty(connnString))
            {
                throw new ArgumentNullException("connnString");
            }

            MainConnectionString = connnString;
            MainDbType           = dbType;
            MainConn             = DBFactory.NewConnection(dbType, connnString);

            return(RefreshMainSqlConnection());
        }
コード例 #9
0
        public static DataTable GetDataTable(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
                                             params DbParameter[] commandParameters)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("conn");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                var cmd = DBFactory.NewCommand(dbType, conn);
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;

                if (commandParameters != null)
                {
                    foreach (var param in commandParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                var     da = DBFactory.NewDataAdapter(dbType, cmd);
                DataSet ds = new DataSet();
                da.Fill(ds); //填充数据
                cmd.Parameters.Clear();
                conn.Close();
                if (ds.Tables.Count > 0)
                {
                    return(ds.Tables[0]);
                }
                else
                {
                    return(null);
                }
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.GetDataSet 【" + cmdText + "】", ex);
                OpenConnection(dbType, conn);  //重新连接
                return(null);
            }
        }
コード例 #10
0
        public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, List <string> sqlList)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("conn");
            }

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                var cmd = DBFactory.NewCommand(dbType, conn);

                using (var trans = conn.BeginTransaction())
                {
                    cmd.Transaction = trans;
                    try
                    {
                        foreach (string sql in sqlList)
                        {
                            if (string.IsNullOrWhiteSpace(sql))
                            {
                                continue;
                            }

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        EventLogger.Log("SqlHelper.ExecuteNonQuerys SQL【" + string.Join(";", sqlList.ToArray()) + "】 ", ex);
                        trans.Rollback();             //回滚
                        OpenConnection(dbType, conn); //重新连接
                        return(ex.Message);
                    }
                }

                conn.Close();
                return("OK");
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.ExecuteNonQuery 错误.", ex);
                OpenConnection(dbType, conn);  //重新连接
                return(ex.Message);
            }
        }
コード例 #11
0
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <param name="conn">连接语句</param>
        /// <returns></returns>
        public static bool OpenConnection(EmDbType dbType, DbConnection conn)
        {
            try
            {
                conn.Open();
                return(true);
            }
            catch (Exception ex)
            {
                string error = $"打开链接失败,连接语句 [{conn.ConnectionString}].";
                EventLogger.Log(error, ex);

                return(false);
            }
        }
コード例 #12
0
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="connString">数据库连接语句</param>
        /// <returns></returns>
        public static bool OpenConnection(EmDbType dbType, string connString)
        {
            try
            {
                DbConnection conn = DBFactory.NewConnection(dbType, connString);
                conn.Open();
                return(true);
            }
            catch (Exception ex)
            {
                string error = $"打开链接失败,连接语句 [{connString}].";
                EventLogger.Log(error, ex);

                return(false);
            }
        }
コード例 #13
0
        /// <summary>
        /// 初始化SQL语句,初始化一次
        /// </summary>
        public static void RefreshDBSetting()
        {
            if ((ConfigurationManager.ConnectionStrings == null) ||
                (ConfigurationManager.ConnectionStrings[_dbConnectName] == null))
            {
                return;
            }

            string connectString = ConfigurationManager.ConnectionStrings[_dbConnectName].ConnectionString;   //获取

            #region 解析连接语句

            string[] _Attrib = connectString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

            Dictionary <string, string> _AttribList = new Dictionary <string, string>();

            for (int i = 0; i < _Attrib.Length; i++)
            {
                string[] _AttribValue = _Attrib[i].Split('=');
                _AttribList.Add(_AttribValue[0].ToLower(), _AttribValue[1]);
            }

            #endregion

            if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName ==
                "Oracle.ManagedDataAccess.Client")
            {
                _dbType          = EmDbType.Oracle;
                _dbDefaultSchema = _AttribList["user id"];
            }
            else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName == "MySql.Data.MySqlClient")
            {
                _dbType = EmDbType.MySql;
            }
            else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName == "System.Data.SqlClient")
            {
                _dbType          = EmDbType.SqlServer;
                _dbDefaultSchema = "dbo";
            }
            else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName ==
                     "JetEntityFrameworkProvider")
            {
                _dbType = EmDbType.Access;
            }

            SqlHelper.InitialMainConn(_dbType, connectString);
        }
コード例 #14
0
        public static string BuildConnStr(EmDbType dbtype, string datasource, string database, string userid, string password)
        {
            string connStr = "";

            switch (dbtype)
            {
            case EmDbType.SqlServer:
                connStr =
                    String.Format(
                        "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
                        datasource, database, userid, password);
                break;

            case EmDbType.Oracle:
                connStr =
                    String.Format("DATA SOURCE={0}:1521/{1};PASSWORD={3};PERSIST SECURITY INFO=True;USER ID={2}",
                                  datasource, database, userid, password); //Oracle.ManagedDataAccess
                //connStr = string.Format("Host={0};Port=1521;User ID={2};Password={3}; Service Name={1}",
                //        datasource, database, userid, password);   //DDTek.Oracle
                break;

            case EmDbType.MySql:
                connStr = String.Format("server={0};user id={2};password={3};database={1}",
                                        datasource, database, userid, password);
                break;

            case EmDbType.Access:
                if (String.IsNullOrEmpty(userid))
                {
                    connStr = String.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Persist Security Info=False;",
                                            datasource);
                }
                else
                {
                    connStr = String.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Persist Security Info=True;" +
                                            "user id={1};Jet OleDb:DataBase Password={2};",
                                            datasource, userid, password);
                }
                break;

            default:     //其他
                connStr = String.Format("data source={0};database={1};user id={2};password={3}",
                                        datasource, database, userid, password);
                break;
            }
            return(connStr);
        }
コード例 #15
0
        public static string ExecuteNonQuerys(EmDbType dbType, string connString, List <string> sqlList)
        {
            if (string.IsNullOrEmpty(connString))
            {
                throw new ArgumentNullException("connString");
            }

            try
            {
                DbConnection conn = DBFactory.NewConnection(dbType, connString);
                return(ExecuteNonQuerys(conn, dbType, sqlList));
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.ExecuteNonQuerys 错误.", ex);
                OpenConnection(dbType, connString);  //重新连接
                return(ex.Message);
            }
        }
コード例 #16
0
        public static string GetFirstValue(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
                                           params DbParameter[] commandParameters)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("conn");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                var cmd = DBFactory.NewCommand(dbType, conn);
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;

                if (commandParameters != null)
                {
                    foreach (var param in commandParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return(val?.ToString());
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.GetFirstValue 【" + cmdText + "】", ex);
                OpenConnection(dbType, conn);  //重新连接
                return(null);
            }
        }
コード例 #17
0
        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbType"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>OK;错误信息</returns>
        public static string ExecuteNonQuery(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
                                             params DbParameter[] commandParameters)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("conn");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                var cmd = DBFactory.NewCommand(dbType, conn);
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;
                if (commandParameters != null)
                {
                    foreach (var param in commandParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                cmd.ExecuteNonQuery(); //执行
                cmd.Parameters.Clear();
                conn.Close();
                return("OK");
            }
            catch (Exception ex)
            {
                EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex);
                OpenConnection(dbType, conn);  //重新连接
                return(ex.Message);
            }
        }
コード例 #18
0
        public static DbDataReader ExecuterReader(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
                                                  params DbParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(connString))
            {
                throw new ArgumentNullException("connString");
            }
            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentNullException("cmdText");
            }

            try
            {
                var conn = DBFactory.NewConnection(dbType, connString);
                conn.Open();

                var cmd = DBFactory.NewCommand(dbType, conn);
                cmd.CommandType = cmdType;
                cmd.CommandText = cmdText;

                if (commandParameters != null)
                {
                    foreach (var param in commandParameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();

                return(dr);
            }
            catch (Exception ex)
            {
                EventLogger.Log("SqlHelper.ExecuterReader 【" + cmdText + "】", ex);
                OpenConnection(dbType, connString);  //重新刷新
                return(null);
            }
        }
コード例 #19
0
        public static DbDataAdapter NewDataAdapter(EmDbType dbtype, DbCommand cmd)
        {
            switch (dbtype)
            {
            case EmDbType.SqlServer:
                if (cmd is SqlCommand)
                {
                    return(new SqlDataAdapter((SqlCommand)cmd));
                }
                throw new ArgumentException("数据库Command的类型不一致", "cmd");

            case EmDbType.Oracle:
                if (cmd is OracleCommand)
                {
                    return(new OracleDataAdapter((OracleCommand)cmd));
                }
                throw new ArgumentException("数据库Command的类型不一致", "cmd");

            case EmDbType.MySql:
                if (cmd is MySqlCommand)
                {
                    return(new MySqlDataAdapter((MySqlCommand)cmd));
                }
                throw new ArgumentException("数据库Command的类型不一致", "cmd");

            case EmDbType.Access:
                if (cmd is OleDbCommand)
                {
                    return(new OleDbDataAdapter((OleDbCommand)cmd));
                }
                throw new ArgumentException("数据库Command的类型不一致", "cmd");

            default:
                throw new ArgumentException("不支持的数据库类型", "dbtype");
            }
        }
コード例 #20
0
        public static DbCommand NewCommand(EmDbType dbtype, DbConnection conn)
        {
            switch (dbtype)
            {
            case EmDbType.SqlServer:
                if (conn is SqlConnection)
                {
                    return(((SqlConnection)conn).CreateCommand());
                }
                throw new ArgumentException("数据库连接的类型不一致", "conn");

            case EmDbType.Oracle:
                if (conn is OracleConnection)
                {
                    return(((OracleConnection)conn).CreateCommand());
                }
                throw new ArgumentException("数据库连接的类型不一致", "conn");

            case EmDbType.MySql:
                if (conn is MySqlConnection)
                {
                    return(((MySqlConnection)conn).CreateCommand());
                }
                throw new ArgumentException("数据库连接的类型不一致", "conn");

            case EmDbType.Access:
                if (conn is OleDbConnection)
                {
                    return(((OleDbConnection)conn).CreateCommand());
                }
                throw new ArgumentException("数据库连接的类型不一致", "conn");

            default:
                throw new ArgumentException("不支持的数据库类型", "dbtype");
            }
        }
コード例 #21
0
        public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, params string[] sqls)
        {
            List <string> sqlList = new List <string>(sqls);

            return(ExecuteNonQuerys(conn, dbType, sqlList));
        }