postgresql的 sqlhelper

算是积累一下自己的代码库，这几天虽然压抑了些，但也算有成长

public string ConnectionString { get; set; }
        public PgHelper(string connStr)
        {
            ConnectionString = connStr;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>成功返回大于0的数字</returns>
        public int ExecuteSQL(string sql)
        {
            int num2 = -1;
            using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
            {
                using (NpgsqlCommand command = new NpgsqlCommand(sql,connection))
                {
                    try
                    {
                        connection.Open();
                        num2=command.ExecuteNonQuery();
                    }
                    catch (NpgsqlException exception)
                    {
                        throw new Exception(string.Format("执行SQL【{0}】出错，详细信息为：{1}",sql,exception.Message));
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return num2;
        }


        /// <summary>
        /// 参数化执行SQL语句
        /// </summary>
        /// <param name="sql">带参数的SQL语句</param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public static int ExecuteParameterSQL(string sql,Dictionary<string,string> paras)
        {
            int num2 = -1;
            string strConn = ConfigurationManager.AppSettings["dbconn"].ToString();

            using (NpgsqlConnection connection = new NpgsqlConnection(strConn))
            {
                using (NpgsqlCommand command = new NpgsqlCommand(sql,connection))
                {
                    try
                    {
                        connection.Open();

                        foreach (string key in paras.Keys)
                        {
                            command.Parameters.Add(key,paras[key]);
                        }

                        num2 = command.ExecuteNonQuery();
                    }
                    catch (NpgsqlException exception)
                    {
                        throw new Exception(string.Format("执行SQL【{0}】出错，详细信息为：{1}",exception.Message));
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return num2;
        }

/// <summary>
        /// 批量执行SQL语句(事务)
        /// </summary>
        /// <param name="lstSql">SQL语句</param>
        /// <returns>true:执行成功，false：执行失败</returns>
        public bool ExecuteTransSQL(List<string> lstSql)
        {
            if (lstSql == null || lstSql.Count == 0)
            {
                return false;
            }
            else
            {
                using (NpgsqlConnection dbConnection = new NpgsqlConnection(ConnectionString))
                {
                    using (NpgsqlCommand command = new NpgsqlCommand())
                    {
                        NpgsqlTransaction ts = null;
                        try
                        {
                            dbConnection.Open();
                            ts = dbConnection.BeginTransaction();
                            command.Connection = dbConnection;
                            int cnt = 0;
                            foreach (string item in lstSql)
                            {
                                if (!String.IsNullOrEmpty(item) && item.Trim().Length > 0)
                                {
                                    command.CommandText = item;
                                    cnt += command.ExecuteNonQuery();
                                }
                            }
                            ts.Commit();
                            return true;
                        }
                        catch (NpgsqlException ex)
                        {
                            if (ts != null)
                            {
                                ts.Rollback();
                            }
                            throw new Exception(string.Format("执行SQL出错:{0}",ex.Message));
                            return false;
                        }
                        finally
                        {
                            dbConnection.Close();
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 参数化批量执行SQL语句（事务）
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public bool ExecuteParameterListSQL(Dictionary<string,string>> dic)
        {
            string strConn = ConnectionString;
            if (dic == null || dic.Count == 0)
            {
                return false;
            }
            else
            {
                NpgsqlTransaction ts = null;
                using (NpgsqlConnection connection = new NpgsqlConnection(strConn))
                {
                    using (NpgsqlCommand command = new NpgsqlCommand())
                    {
                        try
                        {
                            connection.Open();
                            ts = connection.BeginTransaction();
                            command.Connection = connection;
                            foreach (KeyValuePair<string,string>> item in dic)
                            {
                                command.CommandText = item.Key;
                                foreach (KeyValuePair<string,string> para in item.Value)
                                {
                                    command.Parameters.Add(para.Key,para.Value);
                                }
                                command.ExecuteNonQuery();
                            }
                            ts.Commit();
                            return true;
                        }
                        catch (Exception ex)
                        {
                            if (ts != null)
                            {
                                ts.Rollback();
                            }
                            throw ex;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }
        }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!