sql server 连接类

于 2009-07-28 09:15:00 发布
阅读量307 收藏
点赞数
分类专栏： asp.net 文章标签： sql server parameters dataset string character exception
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiezigege/article/details/4385902
版权

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Configuration;

/// <summary>
///Cls_Connection 的摘要说明
/// </summary>
public class Cls_Connection
{
    private String str_Connection;
    public SqlConnection Obj_Conn;
    public SqlTransaction Obj_Tran;
    public Cls_Connection()
    {
        //
        // TODO:
        //
    }
    public SqlConnection ConnectionDB()
    {
        try
        {
            str_Connection = WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            Obj_Conn = new SqlConnection(str_Connection);
            Obj_Conn.Open();
        }
        catch (SqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
        return Obj_Conn;
    }
    //返回DataReader数据集，下面的SQL可以动态生成

    public SqlDataReader Db_CreateReader(string SQL)
    {
        ConnectionDB();

        SqlCommand cmd = new SqlCommand(SQL, Obj_Conn);

        SqlDataReader Rs_Reader = cmd.ExecuteReader();

        return Rs_Reader;

    }
    //返回DataReader数据集，安全的！ 【调用方法】

    //Cr_Custmor = new Cls_Connection();
    //SqlParameter[] para ={
    //                    new SqlParameter("@name",ReplaceBadChar(this.TxtName.Text.Trim())),
    //                     new SqlParameter("@car",ReplaceBadChar(this.TxtCar.Text.Trim()))
    //                  };
    //Sdr_Custmor = Cr_Custmor.Secure_CreateReader("select IndexID from SubscriberInfo where Name=@name and CardId=@car", para);
    //if (Sdr_Custmor.Read())
    //{
    //    Eidtflg = true;
    //    strIndexId = Sdr_Custmor["IndexID"].ToString().Trim();
    //}
    //Sdr_Custmor.Close();
    //Cr_Custmor.DisConnectDB();
    public SqlDataReader Secure_CreateReader(string SQL, SqlParameter[] prams)
    {
        ConnectionDB();

        SqlCommand cmd = new SqlCommand(SQL, Obj_Conn);
        if (prams != null)
        {

            foreach (SqlParameter par in prams)
            {
                cmd.Parameters.Add(par);
            }
        }
        SqlDataReader Rs_Reader = cmd.ExecuteReader();

        return Rs_Reader;

    }
    //返回DataReader数据集，下面的SQL是存储过程

    public SqlDataReader Db_CommandReader(string SQL)
    {
        ConnectionDB();

        SqlCommand cmd = new SqlCommand(SQL, Obj_Conn);

        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataReader Rs_Reader = cmd.ExecuteReader();

        return Rs_Reader;

    }


    //返回数据DataReader数据集，不需要返回数据的修改，删除可以使用本函数

    public bool Db_Execute(string SQL)
    {

        ConnectionDB();

        SqlCommand cmd = new SqlCommand(SQL, Obj_Conn);
        try
        {

            cmd.ExecuteNonQuery();
            return true;

        }
        catch
        {

            DisConnectDB();

            return false;

        }
    }
    //断开连接，释放空间
    public void DisConnectDB()
    {
        Obj_Conn.Close();
        Obj_Conn.Dispose();
        Obj_Conn = null;
    }

}
###############################################3

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Configuration;

namespace Forum.Component
{
    /// <summary>
    ///存储过程的返回值纪录类
    ///     DataSet : 表示返回的表
    ///     Output  : 存储过程的输出参数
    ///     Value   : 存储过程的返回值
    /// </summary>
    public class SqlResult
    {
        public int Value;
        public Hashtable Output;
        public DataSet dataSet;

        public SqlResult()
        {
            Value = 0;
            Output = new Hashtable();

            dataSet = new DataSet();
        }
    }

    /**/
    /// <summary>

    ///     用于调用数据库中的存储过程，返回一个DataSet、Output、Value的SqlResult类
    /// </summary>
    public class SqlProcedure
    {
        public SqlTransaction Obj_Tran;
        private string sp_name;
        private SqlConnection myConnection;
        private SqlCommand myCommand;
        private SqlParameter myParameter;
        private String str_Connection;
        private SqlTransaction SqlTran;

        public string ProcedureName
        {
            get { return this.sp_name; }
            set { this.sp_name = value; }
        }

        public SqlProcedure()
            : this("")
        {
        }

        public SqlProcedure(string sp_name)
        {
            this.ProcedureName = sp_name;
        }
        public SqlConnection ConnectionDB()
        {
            try
            {
                str_Connection = WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
                myConnection = new SqlConnection(str_Connection);
                myConnection.Open();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return myConnection;
        }
        public void CloseConn()
        {
            myConnection.Close();
            myConnection.Dispose();
        }
        public SqlResult Call(params object[] parameters)
        {
            SqlResult result = new SqlResult();

            myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            myCommand = new SqlCommand(this.ProcedureName, myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

            try
            {
                ConnectionDB();
                GetProcedureParameter(parameters);

                myAdapter.Fill(result.dataSet, "Table");

                GetOutputValue(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                myAdapter.Dispose();
                myCommand.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }

            return result;
        }
        public void BeginTrance()
        {
            SqlTran = myConnection.BeginTransaction();
        }
        public void Commit()
        {
            SqlTran.Commit();
        }
        public void Rollback()
        {
            SqlTran.Rollback();
        }
        public SqlResult Call_Tranc(params object[] parameters)
        {
            SqlResult result = new SqlResult();
            myCommand = new SqlCommand(this.ProcedureName, myConnection);
            myCommand.Transaction = SqlTran;
            myCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

            try
            {
                GetProcedureParameter(parameters);

                myAdapter.Fill(result.dataSet, "Table");

                GetOutputValue(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                myAdapter.Dispose();
                myCommand.Dispose();

            }
            return result;
        }

        private void GetProcedureParameter(params object[] parameters)
        {

            SqlCommand myCommand2 = new SqlCommand();

            myCommand2.Connection = this.myConnection;
            myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + this.ProcedureName + "' order by ORDINAL_POSITION";
            myCommand2.Transaction = this.SqlTran;
            SqlDataReader reader = null;
            try
            {
                reader = myCommand2.ExecuteReader();
                myParameter = new SqlParameter();
                myParameter.ParameterName = "@Value";
                myParameter.SqlDbType = SqlDbType.Int;
                myParameter.Direction = ParameterDirection.ReturnValue;

                myCommand.Parameters.Add(myParameter);
                int i = 0;
                while (reader.Read())
                {
                    myParameter = new SqlParameter();
                    myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
                    myParameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;
                    switch (reader["DATA_TYPE"].ToString())
                    {
                        case "bit":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (bool)parameters[i];
                            myParameter.SqlDbType = SqlDbType.Bit;
                            break;

                        case "bigint":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (int)parameters[i];
                            myParameter.SqlDbType = SqlDbType.BigInt;
                            break;

                        case "int":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (int)parameters[i];
                            myParameter.SqlDbType = SqlDbType.Int;
                            break;

                        case "decimal":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (double)parameters[i];
                            myParameter.SqlDbType = SqlDbType.Decimal;
                            myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
                            myParameter.Scale = (byte)reader["NUMERIC_SCALE"];
                            break;

                        case "numeric":
                            if (myParameter.Direction == ParameterDirection.Input)
                                if (parameters[i].GetType() == typeof(double))
                                {
                                    myParameter.Value = (double)parameters[i];
                                    myParameter.SqlDbType = SqlDbType.Float;
                                }
                                else
                                {
                                    myParameter.Value = (int)parameters[i];
                                    myParameter.SqlDbType = SqlDbType.Int;
                                }
                            break;

                        case "nvarchar":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType = SqlDbType.NVarChar;
                            break;

                        case "varchar":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType = SqlDbType.VarChar;
                            break;

                        case "nchar":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType = SqlDbType.NChar;
                            break;

                        case "char":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType = SqlDbType.Char;
                            break;

                        case "ntext":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.SqlDbType = SqlDbType.NText;
                            break;

                        case "text":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (string)parameters[i];
                            myParameter.SqlDbType = SqlDbType.Text;
                            break;

                        case "datetime":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (DateTime)parameters[i];
                            myParameter.SqlDbType = SqlDbType.DateTime;
                            break;
                        case "smalldatetime":
                            if (myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value = (DateTime)parameters[i];
                            myParameter.SqlDbType = SqlDbType.DateTime;
                            break;
                        case "image":
                            if (myParameter.Direction == ParameterDirection.Input)
                            {
                                HttpPostedFile PostedFile = (HttpPostedFile)parameters[i];

                                Byte[] FileByteArray = new Byte[PostedFile.ContentLength];
                                Stream StreamObject = PostedFile.InputStream;
                                StreamObject.Read(FileByteArray, 0, PostedFile.ContentLength);

                                myParameter.Value = FileByteArray;
                            }

                            myParameter.SqlDbType = SqlDbType.Image;
                            break;

                        case "uniqueidentifier":
                            //myParameter.Value = (string)parameters[i];
                            myParameter.SqlDbType = SqlDbType.UniqueIdentifier;
                            break;

                        default: break;
                    }
                    i++;

                    myCommand.Parameters.Add(myParameter);
                }
            }
            catch (Exception e)
            {
                throw e;

            }
            finally
            {
                if (reader != null) reader.Close();
                myCommand2.Dispose();
            }
        }
    private void GetOutputValue(SqlResult result)
        {
            result.Value = (int)myCommand.Parameters["@Value"].Value;

            foreach (SqlParameter parameter in myCommand.Parameters)
            {
                if (parameter.Direction == ParameterDirection.Output)
                {
                    result.Output.Add(parameter.ParameterName, parameter.Value);
                }
            }
        }
    }
}

杰子
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值