数据库操作类
来源:互联网 发布:淘宝商品抓取工具 编辑:程序博客网 时间:2024/05/18 04:50
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration ;namespace Model{ public class Sqlcommand { private static readonly string str = ConfigurationManager.ConnectionStrings["DbRelativeConnectionString"].ConnectionString; /// <summary> /// 执行sql,调用DataReader对象,返回值为SqlDataReader类型 /// </summary> /// <param name="sql">执行的sql语句</param> /// <param name="pars">sql的参数</param> /// <returns>SqlDataReader类型</returns> public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] pars) { SqlDataReader read; using (SqlConnection conn = new SqlConnection(str)) { SqlCommand cmd = new SqlCommand(sql, conn); foreach (SqlParameter par in pars) cmd.Parameters.Add(par); try { conn.Open(); read = cmd.ExecuteReader(); } finally { if (cmd != null) { cmd.Dispose(); } if (conn.State == ConnectionState.Open) { conn.Close(); } } return read; } } /// <summary> /// 执行sql语句,返回值为Dataset类型 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pars">参数</param> /// <returns>返回值为Dataset类型</returns> public static DataSet dataset(string sql,string table) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(str)) { SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter adpt = new SqlDataAdapter(cmd); try { conn.Open(); adpt.Fill(ds, table); } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (cmd != null) { cmd.Dispose(); } if (conn.State == ConnectionState.Open) { conn.Close(); } } } return ds; } /// <summary> /// 执行sql语句,返回值为DataTable类型 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pars">sql中的参数</param> /// <returns>返回值为DataTable类型</returns> public static DataTable ExecuteTable(string sql, params SqlParameter[] pars) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(str)) { //SqlDataAdapter da = new SqlDataAdapter(sql,conn); //foreach (SqlParameter par in pars) // da.SelectCommand .Parameters .Add(par); SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType =CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(cmd); foreach (SqlParameter par in pars) { cmd.Parameters.Add(par); } try { da.Fill(dt); } finally { if (da != null) da.Dispose(); } } return dt; } /// <summary> /// 执行sql语句,返回所关联的行数 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pars">sql的参数</param> /// <returns>int类型</returns> public static int ExecuteNonQuert(string sql, params SqlParameter[] pars) { int t = 0; using (SqlConnection conn=new SqlConnection (str )) { SqlCommand cmd = new SqlCommand(sql, conn); foreach (SqlParameter par in pars) cmd.Parameters.Add(par); try { conn.Open(); t = cmd.ExecuteNonQuery(); } finally { if (cmd != null) cmd.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); } } return t; } /// <summary> /// 用于查询总数目等 /// </summary> /// <param name="sql"></param> /// <param name="pars"></param> /// <returns></returns> public static int ExecuteNumber(string sql, params SqlParameter[] pars) { int t = 0; using (SqlConnection conn = new SqlConnection(str)) { SqlCommand cmd=new SqlCommand (sql,conn); foreach (SqlParameter par in pars) cmd.Parameters.Add(par); try { conn.Open(); t = Convert .ToInt32(cmd.ExecuteScalar()); } finally { if (cmd != null) cmd.Dispose(); if (conn.State == ConnectionState.Open) conn.Close(); } } return t; } /// <summary> /// 字符段的截取 /// </summary> /// <param name="str">字符串</param> /// <param name="length">截取长度</param> /// <returns></returns> public static string Cut(string str, int length) { if (str.Length > length) { string stem=null; for (int i = 0; i < length; i++) { stem += str[i]; } return stem+"……"; } else { return str; } } /// <summary> /// 将text格式简单的转换成HTML格式保存 /// </summary> /// <param name="str"></param> /// <returns></returns> public static string ConverToHtml(string str) { string tem = str; tem.Replace(" ", " "); tem.Replace("<", "<"); tem.Replace(">", ">"); tem .Replace ("\r","<br/>"); tem.Replace("&", "&"); tem .Replace ("/","""); return tem; } }}