postgresql的 sqlhelper

前端之家收集整理的这篇文章主要介绍了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();
                        }
                    }
                }
            }
        }
目前我用的最多的就是:批量插入。以前在用sql的时候没怎么批量插入过,现在发现真的蛮省事的。 原文链接:https://www.f2er.com/postgresql/194227.html

猜你在找的Postgre SQL相关文章