コード例 #1
0
        /// <summary>
        /// 执行带参数的SQL语句,获取首行首列
        /// </summary>
        public object ExecuteScalar(string _ISQL, Dictionary <string, string> _IParms, bool _IsRealData = false)
        {
            object reVal = null;

            using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        string _HashKey = ConnectionString + "-ExecuteScalar-" + _ISQL;
                        foreach (string _FParm in _IParms.Keys)
                        {
                            _HashKey += "-" + _FParm + "-" + _IParms[_FParm];
                        }
                        OwnCacheManage.ClearCache <object>(_CacheOBJ, _CacheOBJTime, CacheConfig.DBSelectTimeOut, _HashKey);
                        if (!_IsRealData && _CacheOBJ.ContainsKey(_HashKey))
                        {
                            reVal = _CacheOBJ[_HashKey];
                        }
                        else
                        {
                            PrepareCommand(cmd, SqlConn, null, CommandType.Text, _ISQL, _IParms);
                            object obj = cmd.ExecuteScalar();
                            cmd.Parameters.Clear();
                            if (!((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))))
                            {
                                reVal = obj;
                                OwnCacheManage.SaveCache <object>(_CacheOBJ, _CacheOBJTime, _HashKey, reVal);
                            }
                        }
                    }
                    catch (MySqlException ex)
                    {
                        string strParms = string.Empty;
                        foreach (string Key in _IParms.Keys)
                        {
                            strParms += Key + ":" + _IParms[Key] + "\r\n";
                        }
                        ex.Data.Add("Sql", _ISQL);
                        ex.Data.Add("SqlParms", strParms);
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (SqlConn.State != ConnectionState.Closed)
                        {
                            SqlConn.Close();
                        }
                    }
                }
            }

            return(reVal);
        }
コード例 #2
0
ファイル: SqlHelper.cs プロジェクト: bnw001/Super.NET
        /// <summary>
        /// 执行带参数的存储过程,获取首行首列
        /// </summary>
        /// <param name="_IStoredProcName">存储过程名称</param>
        /// <param name="_IParms">参数列表</param>
        public object RunProcedureScalar(string _IStoredProcName, DbParameter[] _IParms, bool _IsRealData = false)
        {
            object reVal = null;

            using (SqlConnection SqlConn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        string _HashKey = ConnectionString + "-RunProcedureScalar-" + _IStoredProcName;
                        foreach (DbParameter _FParm in _IParms)
                        {
                            _HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
                        }
                        OwnCacheManage.ClearCache <object>(_CacheOBJ, _CacheOBJTime, CacheConfig.DBSelectTimeOut, _HashKey);
                        if (!_IsRealData && _CacheOBJ.ContainsKey(_HashKey))
                        {
                            reVal = _CacheOBJ[_HashKey];
                        }
                        else
                        {
                            PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
                            object obj = cmd.ExecuteScalar();
                            cmd.Parameters.Clear();
                            if (!((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))))
                            {
                                reVal = obj;
                                OwnCacheManage.SaveCache <object>(_CacheOBJ, _CacheOBJTime, _HashKey, reVal);
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        string strParms = string.Empty;
                        foreach (DbParameter parm in _IParms)
                        {
                            strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
                        }
                        ex.Data.Add("StoredProcedureName", _IStoredProcName);
                        ex.Data.Add("SqlParms", _IParms);
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        if (SqlConn.State != ConnectionState.Closed)
                        {
                            SqlConn.Close();
                        }
                    }
                }
            }

            return(reVal);
        }
コード例 #3
0
        /// <summary>
        /// 执行带参数的存储过程,获取DataTable对象
        /// </summary>
        public DataTable RunProcedureDataTable(string _IStoredProcName, Dictionary <string, string> _IParms, bool _IsRealData = false)
        {
            DataTable reVal    = new DataTable();
            string    _HashKey = ConnectionString + "-RunProcedureDataTable-" + _IStoredProcName;

            foreach (string _FParm in _IParms.Keys)
            {
                _HashKey += "-" + _FParm + "-" + _IParms[_FParm];
            }
            OwnCacheManage.ClearCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, CacheConfig.DBSelectTimeOut, _HashKey);
            if (!_IsRealData && _CacheDataTable.ContainsKey(_HashKey))
            {
                reVal = _CacheDataTable[_HashKey];
            }
            else
            {
                using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
                            MySqlDataReader _reader = cmd.ExecuteReader();
                            reVal.Load(_reader);
                            _reader.Close();
                            _reader.Dispose();
                            cmd.Parameters.Clear();
                            OwnCacheManage.SaveCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, _HashKey, reVal);
                        }
                        catch (MySqlException ex)
                        {
                            string strParms = string.Empty;
                            foreach (string Key in _IParms.Keys)
                            {
                                strParms += Key + ":" + _IParms[Key] + "\r\n";
                            }
                            ex.Data.Add("StoredProcedureName", _IStoredProcName);
                            ex.Data.Add("SqlParms", strParms);
                            throw ex;
                        }
                        finally
                        {
                            cmd.Dispose();
                            if (SqlConn.State != ConnectionState.Closed)
                            {
                                SqlConn.Close();
                            }
                        }
                    }
                }
            }

            return(reVal);
        }
コード例 #4
0
ファイル: SQLiteHelper.cs プロジェクト: bnw001/Super.NET
        /// <summary>
        /// 执行带参数的SQL语句,获取DataTable对象
        /// </summary>
        public DataTable ExecuteDataTable(string _ISQL, DbParameter[] _IParms, bool _IsRealData = false)
        {
            DataTable reVal    = new DataTable();
            string    _HashKey = ConnectionString + "-ExecuteDataTable-" + _ISQL;

            foreach (DbParameter _FParm in _IParms)
            {
                _HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
            }
            OwnCacheManage.ClearCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, CacheConfig.DBSelectTimeOut, _HashKey);
            if (_IsRealData && _CacheDataTable.ContainsKey(_HashKey))
            {
                reVal = _CacheDataTable[_HashKey];
            }
            else
            {
                using (SQLiteConnection sqliteConn = new SQLiteConnection(ConnectionString))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, sqliteConn, null, CommandType.Text, _ISQL, _IParms);
                            SQLiteDataReader _reader = cmd.ExecuteReader();
                            reVal.Load(_reader);
                            _reader.Close();
                            _reader.Dispose();
                            cmd.Parameters.Clear();
                            OwnCacheManage.SaveCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, _HashKey, reVal);
                        }
                        catch (SQLiteException ex)
                        {
                            string strParms = string.Empty;
                            foreach (DbParameter parm in _IParms)
                            {
                                strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
                            }
                            ex.Data.Add("Sql", _ISQL);
                            ex.Data.Add("SqlParms", strParms);
                            throw ex;
                        }
                        finally
                        {
                            cmd.Dispose();
                            if (sqliteConn.State != ConnectionState.Closed)
                            {
                                sqliteConn.Close();
                            }
                        }
                    }
                }
            }

            return(reVal);
        }
コード例 #5
0
        /// <summary>
        /// 执行带参数的SQL语句,获取DataSet对象
        /// </summary>
        public DataSet ExecuteDataSet(string _ISQL, Dictionary <string, string> _IParms, bool _IsRealData = false)
        {
            DataSet reVal    = new DataSet();
            string  _HashKey = ConnectionString + "-ExecuteDataSet-" + _ISQL;

            foreach (string _FParm in _IParms.Keys)
            {
                _HashKey += "-" + _FParm + "-" + _IParms[_FParm];
            }
            OwnCacheManage.ClearCache <DataSet>(_CacheDataSet, _CacheDataSetTime, CacheConfig.DBSelectTimeOut, _HashKey);
            if (!_IsRealData && _CacheDataSet.ContainsKey(_HashKey))
            {
                reVal = _CacheDataSet[_HashKey];
            }
            else
            {
                using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, SqlConn, null, CommandType.Text, _ISQL, _IParms);
                            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                            adapter.Fill(reVal);
                            cmd.Parameters.Clear();
                            OwnCacheManage.SaveCache <DataSet>(_CacheDataSet, _CacheDataSetTime, _HashKey, reVal);
                        }
                        catch (MySqlException ex)
                        {
                            string strParms = string.Empty;
                            foreach (string Key in _IParms.Keys)
                            {
                                strParms += Key + ":" + _IParms[Key] + "\r\n";
                            }
                            ex.Data.Add("Sql", _ISQL);
                            ex.Data.Add("SqlParms", strParms);
                            throw ex;
                        }
                        finally
                        {
                            cmd.Dispose();
                            if (SqlConn.State != ConnectionState.Closed)
                            {
                                SqlConn.Close();
                            }
                        }
                    }
                }
            }

            return(reVal);
        }
コード例 #6
0
        /// <summary>
        /// 执行带参数的存储过程,获取DataSet对象
        /// </summary>
        public DataSet RunProcedureDataSet(string _IStoredProcName, DbParameter[] _IParms, bool _IsRealData = false)
        {
            DataSet reVal    = new DataSet();
            string  _HashKey = ConnectionString + "-RunProcedureDataSet-" + _IStoredProcName;

            foreach (DbParameter _FParm in _IParms)
            {
                _HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
            }
            OwnCacheManage.ClearCache <DataSet>(_CacheDataSet, _CacheDataSetTime, CacheConfig.DBSelectTimeOut, _HashKey);
            if (!_IsRealData && _CacheDataSet.ContainsKey(_HashKey))
            {
                reVal = _CacheDataSet[_HashKey];
            }
            else
            {
                using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
                            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                            adapter.Fill(reVal);
                            cmd.Parameters.Clear();
                            OwnCacheManage.SaveCache <DataSet>(_CacheDataSet, _CacheDataSetTime, _HashKey, reVal);
                        }
                        catch (MySqlException ex)
                        {
                            string strParms = string.Empty;
                            foreach (DbParameter parm in _IParms)
                            {
                                strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
                            }
                            ex.Data.Add("StoredProcedureName", _IStoredProcName);
                            ex.Data.Add("SqlParms", _IParms);
                            throw ex;
                        }
                        finally
                        {
                            cmd.Dispose();
                            if (SqlConn.State != ConnectionState.Closed)
                            {
                                SqlConn.Close();
                            }
                        }
                    }
                }
            }

            return(reVal);
        }