C#的SQLHelper打包

using?System;

using?System.Collections.Generic;

using?System.Text;

using?System.Configuration;

using?System.Data;

using?System.Data.SqlClient;

using?System.Collections;

?

namespace?Utility

{

????public?class?SQLHelper

????{

????????//AppSettings

????????public?static?string?App_SQL_DB?=?ConfigurationManager.AppSettings["SqlCon"];

????????//ConnectionStrings

????????//static?string?Conn_SQL_DB?=?ConfigurationManager.ConnectionStrings["SqlCon"].ToString();

?

????????///?<summary>

????????///?增?删?改方法

????????///?</summary>

????????///?<param?name="cmdType"></param>

????????///?<param?name="sql"></param>

????????///?<param?name="parms"></param>

????????///?<returns></returns>

????????public?static?int?ExecuteNonQuery(CommandType?cmdType,?string?sql,?SqlParameter[]?parms)

????????{

????????????int?result?=?-1;

????????????using?(SqlConnection?con?=?new?SqlConnection(App_SQL_DB))

????????????{

????????????????SqlCommand?cmd?=?new?SqlCommand();

????????????????PrepareSQLCommand(cmdType,?sql,?parms,?con,?cmd);

????????????????result?=?cmd.ExecuteNonQuery();

????????????}

????????????return?result;

????????}

?

????????///?<summary>

????????///?提取共同方法

????????///?</summary>

????????///?<param?name="cmdType"></param>

????????///?<param?name="sql"></param>

????????///?<param?name="parms"></param>

????????///?<param?name="con"></param>

????????///?<param?name="cmd"></param>

????????private?static?void?PrepareSQLCommand(CommandType?cmdType,?string?sql,?SqlParameter[]?parms,?SqlConnection?con,?SqlCommand?cmd)

????????{

????????????cmd.Connection?=?con;

????????????if?(con.State?!=?ConnectionState.Open)

????????????????con.Open();

????????????cmd.CommandText?=?sql;

????????????cmd.CommandType?=?cmdType;

????????????if?(parms?!=?null)

????????????????foreach?(SqlParameter?parm?in?parms)

????????????????{

????????????????????cmd.Parameters.Add(parm);

????????????????}

????????}

?

?

????????///?<summary>

????????///?查询

????????///?</summary>

????????///?<param?name="cmdType"></param>

????????///?<param?name="sql"></param>

????????///?<param?name="parms"></param>

????????///?<returns></returns>

????????public?static?SqlDataReader?ExecuteReader(CommandType?cmdType,?string?sql,?SqlParameter[]?parms)

????????{

????????????SqlConnection?con?=?new?SqlConnection(App_SQL_DB);

????????????SqlCommand?cmd?=?new?SqlCommand();

????????????PrepareSQLCommand(cmdType,?sql,?parms,?con,?cmd);

????????????SqlDataReader?reader?=?cmd.ExecuteReader();

????????????return?reader;

????????}

????????///?<summary>

????????///?读取单个值

????????///?结果集中第一行的第一列；如果结果集为空，则为空引用

????????///?</summary>

????????///?<param?name="cmdType"></param>

????????///?<param?name="sql"></param>

????????///?<param?name="parms"></param>

????????///?<returns></returns>

????????public?static?int?ExecuteScalar(CommandType?cmdType,?string?sql,?SqlParameter[]?parms)

????????{

????????????int?result?=?-1;

????????????//连接数据库

????????????using?(SqlConnection?con?=?new?SqlConnection(App_SQL_DB))

????????????{

????????????????//执行

????????????????SqlCommand?cmd?=?new?SqlCommand();

?

????????????????PrepareSQLCommand(cmdType,?sql,?parms,?con,?cmd);

????????????????result?=?(int)cmd.ExecuteScalar();

????????????????return?result;

????????????}

????????}

?

????????///?<summary>

????????///?事务提交多SQL语句的执行

????????///?<param?name="cmdText">多sql语句的数组</param>

????????///?<returns>true表示成功,false表示失败</returns>

????????///?</summary>

????????public?static?int?ExecuteNonQuery(ArrayList?cmdText)

????????{

????????????int?flag?=?-1;

????????????using?(SqlConnection?connection?=?new?SqlConnection(App_SQL_DB))

????????????{

????????????????SqlCommand?cmd?=?new?SqlCommand();

?

????????????????//SQLCmd.CommandText?=?null;

????????????????//SQLCmd.CommandType?=?cmdType;

?

????????????????//PrepareSQLCommand(cmdType,?null,?parms,?connection,?SQLCmd);

?

????????????????SqlTransaction?Trans;

????????????????connection.Open();

????????????????Trans?=?connection.BeginTransaction();

????????????????try

????????????????{

????????????????????cmd.Connection?=?connection;

????????????????????cmd.Transaction?=?Trans;

?

????????????????????for?(int?i?=?0;?i?<?cmdText.Count;?i++)

????????????????????{

????????????????????????//SQLCmd.CommandText?=?EscapeString(cmdText[i].ToString());

????????????????????????cmd.CommandText?=?cmdText[i].ToString();

????????????????????????flag?=?cmd.ExecuteNonQuery();

????????????????????}

????????????????????Trans.Commit();

????????????????}

????????????????catch?(Exception?e)

????????????????{

?

????????????????????Trans.Rollback();

????????????????}

????????????????finally

????????????????{

????????????????????connection.Close();

????????????????}

????????????}?return?flag;

?

????????}

?

????????///?<summary>

????????///?执行多条SQL语句的方法

????????///?</summary>

????????///?<param?name="SQLStringList"></param>

????????public?static?void?ExecuteNonQuerySQL(ArrayList?SQLStringList)

????????{

????????????using?(SqlConnection?conn?=?new?SqlConnection(App_SQL_DB))

????????????{

????????????????conn.Open();

????????????????SqlCommand?cmd?=?new?SqlCommand();

????????????????cmd.Connection?=?conn;

????????????????SqlTransaction?tx?=?conn.BeginTransaction();

????????????????cmd.Transaction?=?tx;

????????????????try

????????????????{

????????????????????for?(int?n?=?0;?n?<?SQLStringList.Count;?n++)

????????????????????{

????????????????????????string?strsql?=?SQLStringList[n].ToString();

????????????????????????if?(strsql.Trim().Length?>?1)

????????????????????????{

????????????????????????????cmd.CommandText?=?strsql;

????????????????????????????cmd.ExecuteNonQuery();

????????????????????????}

????????????????????}

????????????????????tx.Commit();

????????????????}

????????????????catch?(System.Data.SqlClient.SqlException?E)

????????????????{

????????????????????tx.Rollback();

????????????????????throw?new?Exception(E.Message);

????????????????}

????????????}

????????}

?

????????///?<summary>

????????///?转义字符

????????///?</summary>

????????///?<param?name="str"></param>

????????///?<returns></returns>

????????public?static?string?EscapeString(string?str)

????????{

????????????//??str?=?str.Replace("[lt",?"<");

????????????//?str?=?str.Replace("[gt",?">");

????????????return?str;

????????}

????}

}

?<?xml?version="1.0"?encoding="utf-8"?>