postgresql的 sqlhelper

来源：互联网发布：python 能做界面吗编辑：程序博客网时间：2024/06/16 01:00

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

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}", sql, 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, 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, Dictionary<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的时候没怎么批量插入过，现在发现真的蛮省事的。

1 0