MSSQL操作类

这是我自已常用的MSSQL操作类，个人用着觉得还可以，如果高手有更好的，也可以帖出来供大家分享一下，呵呵！！
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
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);