コード例 #1
0
ファイル: DbHelperSQL.cs プロジェクト: lsfv/queryreport
        public static int ExecuteSql(int UserID, string strCmd, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(strCmd, connection))
                    {
                        connection.Open();

                        cmd.CommandTimeout = CommandTimeout;
                        PreParms(cmdParms, cmd);

                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();         // much like other List<T> implementations. Calling .Clear() before dispose can reduce memory usage
                        ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
                        return(rows);
                    }
                }
                catch (Exception ex)
                {
                    ERRORLOG.Add(ex, UserID);
                    throw;
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
            }
        }
コード例 #2
0
ファイル: DbHelperSQL.cs プロジェクト: lsfv/queryreport
        public static DataTable SafeRunProcedure(int UserID, string storedProcName, params SqlParameter[] cmdParms)
        {
            SqlConnection  connection = new SqlConnection(connectionString);
            SqlTransaction ts         = null;
            SqlDataReader  reader     = null;
            DataTable      result     = new DataTable();

            try
            {
                connection.Open();
                ts = connection.BeginTransaction();
                SqlCommand cmd = new SqlCommand(storedProcName, connection, ts);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandTimeout = CommandTimeout;

                PreParms(cmdParms, cmd);

                reader = cmd.ExecuteReader();
                result.Load(reader);

                ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
            }
            catch (Exception ex)
            {
                ERRORLOG.Add(ex, UserID);
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (ts != null)
                {
                    ts.Rollback();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return(result);
        }
コード例 #3
0
ファイル: DbHelperSQL.cs プロジェクト: lsfv/queryreport
        public static DataSet SafeQuery(int UserID, string strCmd)
        {
            if (!CheckQuery(strCmd))
            {
                throw new SystemException("Illegal query token detected. Execution aborted.");
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet        ds = new DataSet();
                SqlTransaction ts = null;
                try
                {
                    connection.Open();
                    ts = connection.BeginTransaction();
                    SqlDataAdapter command = new SqlDataAdapter(strCmd, connection);
                    command.SelectCommand.Transaction    = ts;
                    command.SelectCommand.CommandTimeout = CommandTimeout;
                    command.Fill(ds, "ds");
                    ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
                }
                catch (Exception ex)
                {
                    ERRORLOG.Add(ex, UserID);
                    throw;
                }
                finally
                {
                    if (ts != null)
                    {
                        ts.Rollback();
                    }
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
                return(ds);
            }
        }