注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

我只是一个人

我爱一个人

 
 
 

日志

 
 

数据库操作  

2011-05-17 12:35:56|  分类: 网页制作--技巧| |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

using System;
using System.Data;
using System.Configuration;
using System.Linq;
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.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;

/// <summary>
///DB 的摘要说明
/// </summary>
public class DB
{
    //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.  
    public static string conString = GetConnectionString("ConnectionString");
    public static SqlConnection Getcon()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNET3WEBDBCONNECTIONSTRING"].ConnectionString);
        return con;
    }
    /// <summary>
    /// 执行更新,添加,删除的操作
    /// </summary>
    /// <param name="sqlstring">用于执行的sql语句</param>
    public void insertnamuber(string sqlstring)
    {

        SqlConnection conn = Getcon();
        SqlCommand comm = new SqlCommand(sqlstring, conn);
       try
       {
           conn.Open();
           comm.ExecuteNonQuery();
        
       }
        catch(Exception ex)
       {
           throw new Exception(ex.Message);
        }
        finally
       {
           conn.Close();
       }
    }
    /// <summary>
    /// 用于执行返回 SqlDataReader对象的函数
    /// </summary>
    /// <param name="sqlstring">需要执行的sql语句</param>
    public  SqlDataReader getdr(string  sqlstring)
    {
        SqlConnection conn = Getcon();
        SqlCommand comm = new SqlCommand(sqlstring, conn);
        SqlDataReader dr = null;
        try
        {
            conn.Open();
            dr = comm.ExecuteReader();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dr;
    }
    /// <summary>
    /// 返回数据库中的记录
    /// </summary>
    /// <returns></returns>
    public DataSet getDs(string sqlstring)
    {
        SqlConnection conn = Getcon();

        DataSet ds = new DataSet();
        SqlDataAdapter da= new SqlDataAdapter(sqlstring, conn);
        try
        {
            conn.Open();
            da.Fill(ds,"soid");

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }


/// <summary>
    ///     分页获取数据库中的数据
/// </summary>
/// <param name="sqlstring">sql</param>
/// <param name="name">别名</param>
/// <param name="pageindex">开始索引</param>
/// <param name="pagesize">每次索取记录数</param>
/// <returns></returns>
    public DataSet getDs(int pageindex, int pagesize,string sqlstring, string name)
    {
        SqlConnection conn = Getcon();

        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sqlstring, conn);
        try
        {
            conn.Open();
            da.Fill(ds,pageindex,pagesize,name);

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }
    /// <summary>
    /// 返回记录数
    /// </summary>
    /// <param name="sqlstring"></param>
    /// <returns></returns>
    public int getNuber(string sqlstring)
    {
        SqlConnection conn = Getcon();
        SqlCommand comm = new SqlCommand(sqlstring, conn);
        int a = 0;
        try
        {
            conn.Open();
            a = int.Parse(comm.ExecuteScalar().ToString());

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return a;
    }
    //实现数据Md5加密
    public static string Md5(string Value)
    {
        string strmd5;
        strmd5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(Value, "Md5");
        strmd5 = strmd5.Substring(8, 14);
        return strmd5;
    }
    ///// <summary>
    ///// 获取连接字符串
    ///// </summary>
    //public static string ConnectionString
    //{
    //    get
    //    {
    //        string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
    //        string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
    //        if (ConStringEncrypt == "true")
    //        {
    //            _connectionString = DESEncrypt.Decrypt(_connectionString);
    //        }
    //        return _connectionString;
    //    }
    //}

 
    /// <summary>
    /// 得到web.config里配置项的数据库连接字符串。
    /// </summary>
    /// <param name="configName"></param>
    /// <returns></returns>
    public static string GetConnectionString(string configName)
    {
        string connectionString = ConfigurationManager.AppSettings[configName];
        //string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
        //if (ConStringEncrypt == "true")
        //{
        //    connectionString = DESEncrypt.Decrypt(connectionString);
        //}
        return connectionString;
    }

 public static string setTimes(string timestring)
    {
        DateTime dt = Convert.ToDateTime(timestring);
        return dt.ToShortDateString().ToString();

    }
    //直接执行返回执行行数
    public static int ExecuteCommand(string safeSql, CommandType type)
    {
        using (SqlConnection con = DB.Getcon())
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand(safeSql, con))
            {
                cmd.CommandType = type;
                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }
    }

    //带参数执行
    public static int ExecuteCommand(string sql, CommandType type, params SqlParameter[] values)
    {
        using (SqlConnection con = DB.Getcon())
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = type;
                if (values != null)
                    cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            }
        }
    }

    //返回第一行第一列(string)
    public static object ReturnStringScalar(string safeSql, CommandType type)
    {
        Object obj = null;

        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(safeSql, con))
            {
                cmd.CommandType = type;
                obj = cmd.ExecuteScalar();

            }
        }

        return obj;
    }

    //返回第一行第一列(int)
    public static int GetScalar(string safeSql, CommandType type)
    {
        using (SqlConnection con = DB.Getcon())
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand(safeSql, con))
            {
                cmd.CommandType = CommandType.Text;
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }
    }

    //返回第一行第一列(int)带参数
    public static int GetScalar(string sql, CommandType type, params SqlParameter[] values)
    {
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = type;
                if (values != null)
                    cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }
    }

    //返回dataReader 
    public static SqlDataReader GetReader(string safeSql, CommandType type, params SqlParameter[] values)
    {
        SqlConnection con = new SqlConnection(conString);
        con.Open();

        SqlCommand cmd = new SqlCommand(safeSql, con);
        cmd.CommandType = type;

        if (values != null)
            cmd.Parameters.AddRange(values);

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }

    //返回DataTable
    public static DataTable GetDataSet(string safeSql, CommandType type)
    {
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(safeSql, con))
            {
                cmd.CommandType = type;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
    }

    //返回DataTable(带参数)
    public static DataTable GetDataSet(string sql, CommandType type, params SqlParameter[] values)
    {
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = type;
                if (values != null)
                    cmd.Parameters.AddRange(values);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
    }
    /// <summary>
    /// 分页
    /// </summary>
    /// <param name="currPage">当前页</param>
    /// <param name="pageSize">一页多少个</param>
    /// <param name="field">查询字段</param>
    /// <param name="table">表名</param>
    /// <param name="where">条件</param>
    /// <param name="whereOther"></param>
    /// <param name="orderBy">排序</param>
    /// <param name="id"></param>
    /// <param name="size"></param>
    /// <param name="order"></param>
    /// <returns></returns>

    public static DataSet GetShowPager(int currPage, int pageSize, string field, string table, string where, string whereOther, string orderBy, string id, string size, string order)
    {
        DataSet ds = new DataSet();
        using (SqlConnection con = Getcon())
        {
            con.Open();
            using (SqlDataAdapter sda = new SqlDataAdapter("SP_ShowoPage", con))
            {
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter[] param = new SqlParameter[]
                {
                    new SqlParameter("@Showo_CurrPage", currPage),
                    new SqlParameter("@Showo_PageSize", pageSize),
                    new SqlParameter("@Showo_Field", field),
                    new SqlParameter("@Showo_Table", table),
                    new SqlParameter("@Showo_Where", where),
                    new SqlParameter("@Showo_WhereOther", whereOther),
                    new SqlParameter("@Showo_OrderBy", orderBy),
                    new SqlParameter("@Showo_Id", id),
                    new SqlParameter("@Showo_Size", size),
                    new SqlParameter("@Showo_Order", order)
                };
                sda.SelectCommand.Parameters.AddRange(param);
                sda.Fill(ds);
                return ds;
            }
        }
    }

    public static DataSet GetPager(int currpage, int pagesize, string columns, string tablename, string where, string searchwhere, string order, string keyid)
    {
        DataSet ds = new DataSet();
        using (SqlConnection con = Getcon())
        {
            con.Open();
            using (SqlDataAdapter sda = new SqlDataAdapter("SP_GetPagerdata", con))
            {
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter[] param = new SqlParameter[]
                {
                  new SqlParameter("@currpage", currpage),
                  new SqlParameter("@pagesize", pagesize),
                  new SqlParameter("@columns", columns),
                  new SqlParameter("@tablename", tablename),
                  new SqlParameter("@where", where),
                  new SqlParameter("@searchwhere", searchwhere),
                  new SqlParameter("@order", order),
                  new SqlParameter("@keyid", keyid)
                };
                sda.SelectCommand.Parameters.AddRange(param);
                sda.Fill(ds);
                return ds;
            }
        }
    }
    /// <summary>
    /// 总数
    /// </summary>
    /// <param name="type"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static int GetDataSetCount(string coluns, string tabname, string where, string order)
    {
        int count = 0;
        using (SqlConnection con = DB.Getcon())
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select_sql", con);
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@coluns", coluns),
                new SqlParameter("@tabname", tabname),
                new SqlParameter("@where", where),
                new SqlParameter("@order", order)
            };
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            return count = (int)cmd.ExecuteScalar();
        }
    }
    /// <summary>
    /// 返回一个的dataset 数据集
    /// </summary>
    /// <param name="coluns"></param>
    /// <param name="tabname"></param>
    /// <param name="where"></param>
    /// <param name="order"></param>
    /// <returns></returns>
    public static DataSet GetDataSet(string coluns, string tabname, string where, string order)
    {
        SqlConnection con = DB.Getcon();
        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter("select_sql", con);
        sda.SelectCommand.CommandType = CommandType.StoredProcedure;
        sda.SelectCommand.Parameters.Add("@coluns", SqlDbType.VarChar);
        sda.SelectCommand.Parameters.Add("@tabname", SqlDbType.VarChar);
        sda.SelectCommand.Parameters.Add("@where", SqlDbType.VarChar);
        sda.SelectCommand.Parameters.Add("@order", SqlDbType.VarChar);
        //给元素赋值
        sda.SelectCommand.Parameters["@coluns"].Value = coluns;
        sda.SelectCommand.Parameters["@tabname"].Value = tabname;
        sda.SelectCommand.Parameters["@where"].Value = where;
        sda.SelectCommand.Parameters["@order"].Value = order;
        DataSet ds = new DataSet();
        con.Close();
        sda.Fill(ds);
        return ds;
    }
    ///// <summary>
    /////执行数据(添加,删除, 修改)
    ///// </summary>
    ///// <param name="sql"></param>
    //public static void Operate(string sql)
    //{
    //    SqlConnection con = DB.Getcon();
    //    con.Open();
    //    SqlCommand cmd = new SqlCommand("exec_sql", con);
    //    cmd.CommandType = CommandType.StoredProcedure;
    //    cmd.Parameters.Add("@sql", SqlDbType.Text);
    //    //给元素赋值
    //    cmd.Parameters["@sql"].Value = sql;
    //    cmd.ExecuteNonQuery();
    //    cmd.Dispose();
    //    con.Close();
    //}
    /// <summary>
    ///执行数据(添加,删除, 修改)
    /// </summary>
    /// <param name="sql"></param>
    public static void Operate(string sql)
    {
        SqlConnection con = DB.Getcon();
        con.Open();
        SqlCommand cmd = new SqlCommand("exec_sql", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@sql", SqlDbType.Text);
        //给元素赋值
        cmd.Parameters["@sql"].Value = sql;
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        con.Close();
    }
}

  评论这张
 
阅读(99)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018