- using System;
- using System.Configuration;
- using System.Collections;
- using System.Data;
- using System.Data.SqlClient;
- namespace MyData { /// <summary> /// 通用数据库类MSSQL /// </summary> public class MSSQL { public static string ConnStr = @"server=电脑名\SQLEXPRESS(或IP,端口);uid=数据库帐号;pwd=数据库密码;database=数据库名;"; //public static string ConnStr = @MyData.Properties.Settings.Default.my_soft_sqlConn; //打开数据库链接 public static SqlConnection Open_Conn(string ConnStr) { try { SqlConnection Conn = new SqlConnection(ConnStr + "Connect Timeout=5;"); Conn.Open(); return Conn; } catch(Exception Ex) { throw Ex; } } //关闭数据库链接 public static void Close_Conn(SqlConnection Conn) { if (Conn != null) { Conn.Close(); Conn.Dispose(); } GC.Collect(); } //运行Sql语句 public static int Run_SQL(string SQL, string ConnStr) { SqlConnection Conn = Open_Conn(ConnStr); SqlCommand Cmd = Create_Cmd(SQL, Conn); try { int result_count = Cmd.ExecuteNonQuery(); Close_Conn(Conn); return result_count; } catch(Exception Ex) { throw Ex; } } // 生成Command对象 public static SqlCommand Create_Cmd(string SQL, SqlConnection Conn) { SqlCommand Cmd = new SqlCommand(SQL, Conn); return Cmd; } // 运行Sql语句返回 DataTable public static DataTable Get_DataTable(string SQL, string ConnStr, string Table_name) { SqlConnection Conn = Open_Conn(ConnStr); SqlDataAdapter Da = new SqlDataAdapter(SQL, Conn); DataTable dt = new DataTable(Table_name); Da.Fill(dt); Close_Conn(Conn); return dt; } // 运行Sql语句返回 SqlDataReader对象 public static SqlDataReader Get_Reader(string SQL, string ConnStr) { SqlConnection Conn = Open_Conn(ConnStr); SqlCommand Cmd = Create_Cmd(SQL, Conn); SqlDataReader Dr; try { Dr = Cmd.ExecuteReader(CommandBehavior.Default); } catch { throw new Exception(SQL); } Close_Conn(Conn); return Dr; } // 运行Sql语句返回 SqlDataAdapter对象 public static SqlDataAdapter Get_Adapter(string SQL, string ConnStr) { SqlConnection Conn = Open_Conn(ConnStr); SqlDataAdapter Da = new SqlDataAdapter(SQL, Conn); return Da; } // 运行Sql语句,返回DataSet对象 public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds) { SqlConnection Conn = Open_Conn(ConnStr); SqlDataAdapter Da = new SqlDataAdapter(SQL, Conn); try { Da.Fill(Ds); } catch(Exception Ex) { throw Ex; } Close_Conn(Conn); return Ds; } // 运行Sql语句,返回DataSet对象 public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, string tablename) { SqlConnection Conn = Open_Conn(ConnStr); SqlDataAdapter Da = new SqlDataAdapter(SQL, Conn); try { Da.Fill(Ds, tablename); } catch(Exception Ex) { throw Ex; } Close_Conn(Conn); return Ds; } // 运行Sql语句,返回DataSet对象,将数据进行了分页 public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, int StartIndex, int PageSize, string tablename) { SqlConnection Conn = Open_Conn(ConnStr); SqlDataAdapter Da = new SqlDataAdapter(SQL, Conn); try { Da.Fill(Ds, StartIndex, PageSize, tablename); } catch(Exception Ex) { throw Ex; } Close_Conn(Conn); return Ds; } // 返回Sql语句执行结果的第一行第一列 public static string Get_Row1_Col1_Value(string SQL, string ConnStr) { SqlConnection Conn = Open_Conn(ConnStr); string result; SqlDataReader Dr; try { Dr = Create_Cmd(SQL, Conn).ExecuteReader(); if (Dr.Read()) { result = Dr[0].ToString(); Dr.Close(); } else { result = ""; Dr.Close(); } } catch { throw new Exception(SQL); } Close_Conn(Conn); return result; } }}
将上面的代码保存为 MSSQL.cs 类文件,可以用以下代码进行调用
1、查询
string SQL = "select * from 表名 where 条件";
DataTable dt = MSSQL.Get_DataTable(SQL, MSSQL.ConnStr,"DataTable 表名或空 ");
2、添加、修改、删除
string SQL = "添加、修改、删除 SQL 语句";
MSSQL.Run_SQL(SQL, MSSQL.ConnStr);
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: