1 using System;
2 using System.Collections.Generic;
3 using System.Data.SqlClient;
4 using System.Configuration;
5 using System.Data;
6
7 namespace Common
8 {
9 public abstract class DbHelperSQL
10 {
11 //格式化字符串
12 public static string inSQL(string formatStr)
13 {
14 string Str = formatStr;
15 if (formatStr != null && formatStr != string.Empty)
16 {
17 Str = Str.Replace("'", "''");
18 }
19 return Str;
20 }
21
22 //获取连接字符串
23 public static string ConnectionString
24 {
25 get
26 {
27 string _connectionstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
28 string ConStringEncrypt = ConfigurationManager.AppSettings["ApplicationServices"];
29 if (ConStringEncrypt =="true")
30 {
31 _connectionstring = DESEncrypt.Encrypt(_connectionstring);
32 }
33 return _connectionstring;
34 }
35 }
36
37 #region 执行带参数的SQL语句
38
39 // 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
40 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
41 {
42 SqlConnection connection = new SqlConnection(ConnectionString);
43 SqlCommand cmd = new SqlCommand();
44 try
45 {
46 PrepareCommand(cmd,connection,null,SQLString,cmdParms);
47 SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
48 cmd.Parameters.Clear();
49 return myReader;
50 }
51 catch(System.Data.SqlClient.SqlException e)
52 {
53 throw e;
54 }
55 }
56
57 // 执行SQL语句，返回影响的记录数
58 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
59 {
60 using (SqlConnection connection = new SqlConnection(ConnectionString))
61 {
62 using (SqlCommand cmd = new SqlCommand())
63 {
64 try
65 {
66 PrepareCommand(cmd,connection,null,SQLString,cmdParms);
67 int rows=cmd.ExecuteNonQuery();
68 cmd.Parameters.Clear();
69 return rows;
70 }
71 catch(System.Data.SqlClient.SqlException e)
72 {
73 throw e;
74 }
75 }
76 }
77 }
78
79 // 执行查询语句，返回DataSet
80 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
81 {
82 if (conn.State != ConnectionState.Open)
83 conn.Open();
84 cmd.Connection = conn;
85 cmd.CommandText = cmdText;
86 if (trans != null)
87 cmd.Transaction = trans;
88 cmd.CommandType = CommandType.Text;
89 if (cmdParms != null)
90 {
91 foreach (SqlParameter parameter in cmdParms)
92 {
93 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
94 (parameter.Value == null))
95 {
96 parameter.Value = DBNull.Value;
97 }
98 cmd.Parameters.Add(parameter);
99 }
100 }
101 }
102
103 #endregion
104 }
105 }
106
代码
