sqlhelper

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 
 7 namespace DAL
 8 {
 9     using System.Configuration;
10     using System.Data;
11     using System.Data.SqlClient;
12      class SQLhelper
13     {
14          static readonly string Connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
15 
16          public static DataTable ExecuteTable(string sql, CommandType cmdtype, params SqlParameter[] ps)
17          {
18              SqlDataAdapter da = new SqlDataAdapter(sql, Connstr);
19              da.SelectCommand.Parameters.AddRange(ps);
20              da.SelectCommand.CommandType = cmdtype;
21              DataTable dt = new DataTable();
22              da.Fill(dt);
23              return dt;
24          }
25 
26          public static int ExecNonquery(string sql, CommandType cmdtype, params SqlParameter[] ps)
27          {
28              try
29              {
30                  using (SqlConnection conn = new SqlConnection(Connstr))
31                  {
32                      conn.Open();
33                      SqlCommand cmd = new SqlCommand(sql, conn);
34                      cmd.CommandType = cmdtype;
35                      cmd.Parameters.AddRange(ps);
36                      return cmd.ExecuteNonQuery();
37                  }
38              }
39              catch (Exception ex)
40              {
41                  throw new Exception(ex.Message);
42              }
43          }
44     }
45 }