sqlhelper.cs

Internet聊天室,学习网络聊天实例

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

namespace Web_Speaking.DataAccesslayer
{
    public class SQLHelper
    {
        string _str_sqlcon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["sqlconn"].ToString();
        public DataSet DataSet(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(_str_sqlcon))
            {
                try
                {
                    conn.Open();
                    using (SqlDataAdapter adp = new SqlDataAdapter(sql, conn))
                    {
                        adp.Fill(ds);
                        adp.Dispose();
                    }
                    conn.Close();
                }
                catch (System.Data.SqlClient.SqlException Ex)
                {
                    throw new Exception(Ex.ToString());

                }
                finally
                {
                    conn.Close();
                }
            }
            return ds;
        }



        public void operatorConn(string CommandText)
        {
            using (SqlConnection Connection = new SqlConnection(_str_sqlcon))
            {
                try
                {
                    Connection.Open();
                    using (SqlCommand Command = new SqlCommand(CommandText, Connection))
                    {
                        Command.ExecuteNonQuery();
                    }
                }
                catch (System.Data.SqlClient.SqlException Ex)
                {
                    throw new Exception(Ex.ToString());
                }
                finally
                {
                    Connection.Close();
                }
            }
        }

        /// <param name="str_proname">存储过程名</param>
        /// <param name="cmd_list">输入参数</param>
        /// <param name="out_list">输出参数</param>
        /// <param name="result_list">返回获得参数的值</param>
        public void Admin_login(string str_proname, SortedList cmd_list, SortedList out_list, ref SortedList result_list)  //存储过程参数
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_str_sqlcon))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = str_proname;
                        cmd.Connection = conn;
                        foreach (DictionaryEntry cmd_item in cmd_list)
                        {
                            cmd.Parameters.AddWithValue(cmd_item.Key.ToString(), cmd_item.Value);
                        }
                        SqlParameter[] pa = new SqlParameter[out_list.Count];
                        int i = 0;
                        foreach (DictionaryEntry en in out_list)
                        {
                            pa[i] = new SqlParameter();
                            pa[i].Direction = ParameterDirection.Output;
                            pa[i].Size = 100;
                            pa[i].ParameterName = en.Key.ToString();
                            cmd.Parameters.Add(pa[i]);
                            i++;
                        }
                        cmd.ExecuteNonQuery();

                        for (i = 0; i < pa.Length; i++)
                        {
                            result_list.Add(i, pa[i].Value);
                        }
                    }
                    conn.Close();
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {

            }
        }
    }
}