Esempio n. 1
0
        /// <summary>
        /// 执行查询或存储过程,并返回一个 DataTable 对象。
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public SQLQueryResult <DataTable> SQLExecuteTable(SqlCommand cmd)
        {
            SQLQueryResult <DataTable> result = new SQLQueryResult <DataTable>();
            SqlConnection con = new SqlConnection(_ConnStr);

            try
            {
                cmd.Connection = con;
                using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
                {
                    con.Open();
                    DataSet ds = new DataSet();
                    ad.Fill(ds);
                    result.QueryObject = ds.Tables[0];
                    result.Result      = SQLResultCode.Successful;
                    result.Message     = string.Empty;
                }
            }
            catch (SqlException ex)
            {
                con.Close();
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCodeResolver.GetFromSqlExceptionNumber(ex.Number);
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                con.Close();
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCode.Fail;
                result.Message = ex.Message;
            }

            return(result);
        }
Esempio n. 2
0
 /// <summary>
 /// 执行SQL语句,返回受影响的行数
 /// </summary>
 /// <param name="cmdText"></param>
 /// <returns></returns>
 public SQLQueryResult <int> SQLExecuteNonQuery(string cmdText)
 {
     try
     {
         using (SqlConnection con = new SqlConnection(_ConnStr))
         {
             using (SqlCommand cmd = new SqlCommand(cmdText, con))
             {
                 con.Open();
                 int count = cmd.ExecuteNonQuery();
                 con.Close();
                 return(new SQLQueryResult <int>(SQLResultCode.Successful, count));
             }
         }
     }
     catch (SqlException ex)
     {
         Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
         return(new SQLQueryResult <int>(SQLResultCodeResolver.GetFromSqlExceptionNumber(ex.Number), ex.Message, 0));
     }
     catch (Exception ex)
     {
         Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
         return(new SQLQueryResult <int>(SQLResultCode.Fail, ex.Message, 0));
     }
 }
Esempio n. 3
0
        /// <summary>
        /// 执行查询或存储过程,并返回一个 SqlDataReader 对象,注意使用完 SqlDataReader 对象后必须使用close释放SqlDataReader连接
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public SQLQueryResult <SqlDataReader> SQLExecuteReader(SqlCommand cmd)
        {
            SQLQueryResult <SqlDataReader> result = new SQLQueryResult <SqlDataReader>();
            SqlConnection con = new SqlConnection(_ConnStr);

            try
            {
                cmd.Connection = con;
                con.Open();
                result.QueryObject = cmd.ExecuteReader();
                result.Result      = SQLResultCode.Successful;
                result.Message     = string.Empty;
            }
            catch (SqlException ex)
            {
                con.Close();
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCodeResolver.GetFromSqlExceptionNumber(ex.Number);
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                con.Close();
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCode.Fail;
                result.Message = ex.Message;
            }

            return(result);
        }
Esempio n. 4
0
        /// <summary>
        /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public SQLQueryResult <object> SQLExecuteScalar(string cmdText)
        {
            SQLQueryResult <object> result = new SQLQueryResult <object>();

            try
            {
                using (SqlConnection con = new SqlConnection(_ConnStr))
                {
                    using (SqlCommand cmd = new SqlCommand(cmdText, con))
                    {
                        con.Open();
                        object o = cmd.ExecuteScalar();
                        con.Close();
                        result.Result      = SQLResultCode.Successful;
                        result.Message     = string.Empty;
                        result.QueryObject = o;
                    }
                }
            }
            catch (SqlException ex)
            {
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCodeResolver.GetFromSqlExceptionNumber(ex.Number);
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
                result.Result  = SQLResultCode.Fail;
                result.Message = ex.Message;
            }

            return(result);
        }
Esempio n. 5
0
 /// <summary>
 /// 执行多个SQL语句
 /// </summary>
 /// <param name="cmdTexts">SQL语句</param>
 /// <param name="transaction">是否启用事务</param>
 /// <returns></returns>
 public SQLCommandResult SQLExecuteNonQuery(List <string> cmdTexts, bool transaction)
 {
     try
     {
         using (SqlConnection con = new SqlConnection(_ConnStr))
         {
             con.Open();
             if (transaction)
             {
                 using (SqlTransaction tran = con.BeginTransaction())
                 {
                     using (SqlCommand cmd = con.CreateCommand())
                     {
                         cmd.Connection  = con;
                         cmd.Transaction = tran;
                         foreach (string cmdText in cmdTexts)
                         {
                             cmd.CommandText = cmdText;
                             cmd.ExecuteNonQuery();
                         }
                         tran.Commit();
                         con.Close();
                         return(new SQLCommandResult(SQLResultCode.Successful));
                     }
                 }
             }
             else
             {
                 using (SqlCommand cmd = con.CreateCommand())
                 {
                     cmd.Connection = con;
                     foreach (string cmdText in cmdTexts)
                     {
                         cmd.CommandText = cmdText;
                         cmd.ExecuteNonQuery();
                     }
                     con.Close();
                     return(new SQLCommandResult(SQLResultCode.Successful));
                 }
             }
         }
     }
     catch (SqlException ex)
     {
         Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
         return(new SQLCommandResult(SQLResultCodeResolver.GetFromSqlExceptionNumber(ex.Number), ex.Message));
     }
     catch (Exception ex)
     {
         Ralid.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
         return(new SQLCommandResult(SQLResultCode.Fail, ex.Message));
     }
 }