• C# PGSQL 关于SQL参数的例示


    工作关系 同时使用SQL SERVER 与Postgresql 数据库,

                clientChar = txtClientChar.Text.Trim();
                sql = "select client_id,client_code,name,client_level_id,client_custom_options_ids, area_id,location ";
                sql += " from client where client_code = @pClient_Code or name like @pName";
                NpgsqlParameter[] sqlPara = new NpgsqlParameter[2];
                //pgsql 的参数与SQL SERVER 的格式不一样
                sqlPara[0] = new NpgsqlParameter("@pClient_Code", NpgsqlTypes.NpgsqlDbType.Text,20);
                sqlPara[0].Value = clientChar;
                sqlPara[1] = new NpgsqlParameter("@pName", NpgsqlTypes.NpgsqlDbType.Text,100);
                sqlPara[1].Value = '%' + clientChar + '%';
                DataTable dt = DBHelperPg.ExecuteDataTable(sql, sqlPara);
                dataGridView1.DataSource = dt;

    附:DBHelperpg.cs

     1  class DBHelperPg
     2     {
     3 
     4         public static string ConnectionString = ConfigurationManager.AppSettings["postgre"].ToString();
     5 
     6 
     7         /// <summary>  
     8         /// 执行SQL语句  
     9         /// </summary>  
    10         /// <param name="sql">SQL</param>  
    11         /// <returns>成功返回大于0的数字</returns>  
    12         public static int ExecuteSQL(string sql)
    13         {
    14             int num2 = -1;
    15             using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
    16             {
    17                 using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
    18                 {
    19                     try
    20                     {
    21                         connection.Open();
    22                         num2 = command.ExecuteNonQuery();
    23                     }
    24                     catch (NpgsqlException exception)
    25                     {
    26                         throw new Exception(exception.Message);
    27                     }
    28                     finally
    29                     {
    30                         connection.Close();
    31                     }
    32                 }
    33             }
    34             return num2;
    35         }
    36 
    37         //带参数的执行查询,不返回结果,返回影响行数
    38         //执行SQL语句并返回受影响的行数
    39         public static int ExecuteNonQuery(string sql, params NpgsqlParameter[] parameters)
    40         {
    41             using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
    42             {
    43                 conn.Open();
    44                 using (NpgsqlCommand cmd = conn.CreateCommand())
    45                 {
    46                     cmd.CommandText = sql;
    47                     //foreach (SqlParameter param in parameters)
    48                     //{
    49                     //    cmd.Parameters.Add(param);
    50                     //}
    51                     cmd.Parameters.AddRange(parameters);
    52                     return cmd.ExecuteNonQuery();
    53                 }
    54             }
    55         }
    56 
    57         //执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
    58         public static object ExecuteScalar(string sql, params NpgsqlParameter[] parameters)
    59         {
    60             using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
    61             {
    62                 conn.Open();
    63                 using (NpgsqlCommand cmd = conn.CreateCommand())
    64                 {
    65                     cmd.CommandText = sql;
    66                     cmd.Parameters.AddRange(parameters);
    67                     return cmd.ExecuteScalar();
    68 
    69                 }
    70             }
    71         }
    72 
    73 
    74 
    75 
    76         //查询并返回结果集DataTable,一般只用来执行查询结果比较少的sql。
    77         public static DataTable ExecuteDataTable(string sql, params NpgsqlParameter[] parameters)
    78         {
    79             using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
    80             {
    81                 conn.Open();
    82                 using (NpgsqlCommand cmd = conn.CreateCommand())
    83                 {
    84                     cmd.CommandText = sql;
    85                     cmd.Parameters.AddRange(parameters);
    86 
    87                     NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
    88                     DataSet dataset = new DataSet();
    89                     adapter.Fill(dataset);
    90                     return dataset.Tables[0];
    91                 }
    92             }
    93 
    94             //查询较大的数据用 DateRead(),但应尽可能用分页数据,仍然用datatable更好。
    95         }
    96     }
  • 相关阅读:
    JDK中的主要包
    package

    参数传值机制
    静态初始化块
    static 关键字
    this关键字
    开发中容易造成内存泄露的操作
    通用的分代垃圾回收机制
    JVM调优和Full GC
  • 原文地址:https://www.cnblogs.com/lrzy/p/15867667.html
Copyright © 2020-2023  润新知