예제 #1
0
        ///// <summary>
        ///// 返回DataTable,参数带有状态码的存储过程 oracle的存储过程参数必须定义输出参数rcode和dt游标类型,且名字不能改
        ///// </summary>
        ///// <param name="cmdText"></param>
        ///// <param name="rcode">返回码</param>
        ///// <param name="parameters"></param>
        ///// <returns></returns>
        //public static DataTable ExecuteDataTableByProcSQL(string cmdText, ref int rcode, params MySqlParameter[] parameters)
        //{
        //    DataTable dt = new DataTable();
        //    using (MySqlConnection conn = new MySqlConnection(connstr))
        //    {

        //        using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
        //        {
        //            try
        //            {
        //                conn.Open();
        //                cmd.CommandType = CommandType.StoredProcedure;
        //                cmd.Parameters.AddRange(parameters);
        //                cmd.Parameters.Add(new MySqlParameter("rcode", MySqlDbType.Int32));
        //                cmd.Parameters["rcode"].Direction = ParameterDirection.Output;
        //                cmd.Parameters.Add(new MySqlParameter("dt", MySqlDbType.c));
        //                cmd.Parameters["dt"].Direction = ParameterDirection.Output;
        //                //这里没有@符号:区别SqlServer的写法,Sql中返回结果集可以直接使用select语句,
        //                //而Orcale中返回结果集要有游标
        //                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        //                da.Fill(dt);
        //                rcode = Convert.ToInt32(cmd.Parameters["rcode"].Value);
        //            }
        //            catch (MySqlException ex)
        //            {
        //                log.WriteLog(ex);
        //                return null;
        //            }
        //        }
        //    }
        //    return dt;
        //}

        /// <summary>
        /// 调用存储过程返回DataTable,输出参数为dt的游标类型
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTableByProcSQL(string cmdText, params MySqlParameter[] parameters)
        {
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (!parameters.IsNullEmpty())
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                        da.Fill(dt);
                    }
                    catch (MySqlException ex)
                    {
                        DbLog.WriteLog(ex);
                        return(null);
                    }
                }
            }
            return(dt);
        }
예제 #2
0
        /// <summary>
        /// 执行存储过程返回一系列字符 输出参数必须定义为o_resStr
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string ExecuteStringByProcSQL(string cmdText, params MySqlParameter[] parameters)
        {
            string str = "";

            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters);
                        cmd.Parameters.Add(new MySqlParameter("o_resStr", MySqlDbType.VarChar, 4000)); //这里需要设置长度
                        cmd.Parameters["o_resStr"].Direction = ParameterDirection.Output;
                        cmd.ExecuteNonQuery();                                                         //执行成功返回默认影响行数为1
                        str = Convert.ToString(cmd.Parameters["o_resStr"].Value);
                    }
                    catch (MySqlException ex)
                    {
                        DbLog.WriteLog(ex);
                        return(ex.Message.ToString());
                    }
                }
            }
            return(str);
        }
예제 #3
0
        // connectionString="Server=localhost;Database=drivetop_base; User=otnp80;Password=123;Use Procedure Bodies=false;Charset=utf8;Allow Zero Datetime=True; Pooling=True; Max Pool Size=50; "

        #region 基本操作
        /// <summary>
        /// 查询返回DataTable
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string cmdText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                try
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            adapter.Fill(dt);
                            cmd.Parameters.Clear();
                            return(dt);
                        }
                    }
                }
                catch (MySqlException e)
                {
                    DbLog.WriteLog(e);
                    return(null);
                }
            }
        }
예제 #4
0
 /// <summary>
 ///  批量执行多条SQL语句,实现数据库事务,不好用 没考虑特殊字符。
 /// </summary>
 /// <param name="SQLStringList">List集合中是多条SQL语句</param>
 public static int ExecuteSqlTran(List <String> SQLStringList)
 {
     using (MySqlConnection conn = new MySqlConnection(connstr))
     {
         conn.Open();
         MySqlCommand cmd = new MySqlCommand();
         cmd.Connection = conn;
         MySqlTransaction tx = conn.BeginTransaction();
         cmd.Transaction = tx;
         try
         {
             int count = 0;
             for (int n = 0; n < SQLStringList.Count; n++)
             {
                 string strsql = SQLStringList[n];
                 if (strsql.Trim().Length > 1)
                 {
                     cmd.CommandText = strsql;
                     count          += cmd.ExecuteNonQuery();
                 }
             }
             tx.Commit();
             return(count);
         }
         catch (MySqlException e)
         {
             DbLog.WriteLog(e);
             tx.Rollback();
             return(0);
         }
     }
 }
예제 #5
0
 /// <summary>
 /// 返回首行首列--与ExecuteScalar一样
 /// </summary>
 /// <param name="SQLString"></param>
 /// <param name="parameters"></param>
 /// <returns></returns>
 public static object GetSingle(string SQLString, params MySqlParameter[] parameters)
 {
     using (MySqlConnection conn = new MySqlConnection(connstr))
     {
         using (MySqlCommand cmd = new MySqlCommand(SQLString, conn))
         {
             try
             {
                 conn.Open();
                 cmd.Parameters.AddRange(parameters);
                 object obj = cmd.ExecuteScalar();
                 cmd.Parameters.Clear();
                 if ((Object.Equals(obj, null)) || (Object.Equals(obj, DBNull.Value)))
                 {
                     return(null);
                 }
                 else
                 {
                     return(obj);
                 }
             }
             catch (Exception e)
             {
                 conn.Close();
                 DbLog.WriteLog(e);
                 return(null);
             }
         }
     }
 }
예제 #6
0
        /// <summary>
        /// //批量执行sql,实现事务 返回>=1 成功
        /// </summary>
        /// <param name="sqls">多条sql</param>
        /// <param name="param">sql参数化</param>
        /// <returns></returns>
        public static int BatchExecuteBySql(object[] sqls, object[] param)
        {
            int num = 0;

            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int i = 0; i < sqls.Length; i++)
                    {
                        StringBuilder builder = (StringBuilder)sqls[i];
                        if (builder != null)
                        {
                            cmd.CommandText = builder.ToString();
                            if (param != null)
                            {
                                SqlParameter[] paramArray = (SqlParameter[])param[i];
                                cmd.Parameters.AddRange(paramArray);
                            }
                            num += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (Exception e)
                {
                    num = 0;
                    DbLog.WriteLog(e);
                    tx.Rollback();
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                    tx.Dispose();
                }
            }
            return(num);
        }
예제 #7
0
 public static MySqlDataReader ExecuteDataReader(string cmdText, params MySqlParameter[] parameters)//这里先不用using 调用转换时使用了using
 {
     try
     {
         MySqlConnection conn = new MySqlConnection(connstr);
         MySqlCommand    cmd  = conn.CreateCommand();
         conn.Open();
         cmd.CommandText = cmdText;
         cmd.Parameters.AddRange(parameters);
         MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         cmd.Parameters.Clear();
         return(dr);
     }
     catch (MySqlException ex)
     {
         DbLog.WriteLog(ex);
         return(null);
     }
 }
예제 #8
0
 /// <summary>
 /// 返回影响的行数
 /// </summary>
 /// <param name="cmdText"></param>
 /// <param name="parameters"></param>
 /// <returns></returns>
 public static int ExecuteNonQuery(string cmdText, params MySqlParameter[] parameters)
 {
     using (MySqlConnection conn = new MySqlConnection(connstr))
     {
         using (MySqlCommand cmd = conn.CreateCommand())
         {
             try
             {
                 conn.Open();
                 cmd.CommandText = cmdText;
                 cmd.Parameters.AddRange(parameters);
                 int result = cmd.ExecuteNonQuery();
                 cmd.Parameters.Clear();
                 return(result);
             }
             catch (MySqlException e)
             {
                 DbLog.WriteLog(e);
                 return(-1);
             }
         }
     }
 }