C#版SQL数据库操作类

废话说少吧，发代码才是硬道理。。。

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TestCSharpBasic
{
class SQLHelper
{
private string strError = null;
private int intCount = 0;
public SQLHelper()
{
}
/// <summary>
/// 记录sql日志，日志分等级，level=1是信息，等于0为异常
/// </summary>
/// <param name="str">日志信息</param>
/// <param name="level">日志等级</param>
public void Log(string str,int level)
{
string levelStr = "信息:";
if (level == 1)
{
levelStr = "信息:";
}
else
{
levelStr = "错误:";
}
Console.WriteLine(levelStr + str);
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns>数据库连接对象SqlConnection</returns>
public SqlConnection GetConn()
{// connstr = "Server=(local);DataBase=GlobalMeetings;Uid=sa;pwd=00"
string connStr = "Data Source=.\\SQLEXPRESS;Database=MyStockDB;Integrated Security=SSPI;";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
}
catch (Exception ex)
{
Log(ex.Message,0);
// throw;
}
return conn;
}
/// <summary>
/// 公开属性ErrorMessage，反悔错误信息
/// </summary>
public string ErrorMessage
{
get
{
return strError;
}
}
/// <summary>
/// 查询数据库到dataset中
/// </summary>
/// <param name="SelectString">查询语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>数据结果集</returns>
public DataSet Select(string SelectString, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = GetConn();
}
else
{
conn = sqlConn;
}
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand selectCmd = new SqlCommand(SelectString, conn);
selectCmd.CommandType = CommandType.Text;
adapter.SelectCommand = selectCmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
catch (Exception e)
{
strError = "数据检索失败，错误信息：" + e.Message;
return null;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
// 更新 ，删除，插入
public bool Update(string UpdateString, SqlConnection SqlConn)
{
return udiDataBase(UpdateString,SqlConn);
}
public bool Delete(string DeleteString, SqlConnection SqlConn)
{
return udiDataBase(DeleteString, SqlConn);
}
public bool Insert(string InsertString, SqlConnection SqlConn)
{
return udiDataBase(InsertString, SqlConn);
}
// 更新数据库
public bool udiDataBase(string UDIString, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = GetConn();
}
else
{
conn = sqlConn;
}
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand selectCmd = new SqlCommand(UDIString, conn);
selectCmd.CommandType = CommandType.Text;
intCount = selectCmd.ExecuteNonQuery();
return !(intCount<1);
}
catch (Exception e)
{
strError = "数据检索失败，错误信息：" + e.Message;
return false;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}

}
}

------------------------------------------调用部分---------------------------------------------------

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TestCSharpBasic
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("------start------");
// Check_User(username:"Mr.Chen");
// TestConn();
//测试表结构
/***
* Create Table Users
* (ID int IDENTITY PRIMARY KEY
* UserName nvarchar(50)
* PassWord nvarchar(50)
* );
* **/
// 查询
SQLHelper hp = new SQLHelper();
SqlConnection conn = hp.GetConn();
DataSet ds = hp.Select("select * from users",conn);
for (int a = 0; a < ds.Tables[0].Rows.Count;a++ )
{
Console.WriteLine(" ID：{0} UserName：{1},Password：{2} ",
ds.Tables[0].Rows[a][0],
ds.Tables[0].Rows[a][1],
ds.Tables[0].Rows[a][2]);
}
// 插入数据
bool exec;
string insertSql = "insert into users(username,password) values ('chen1','111')";
string updateSql = "update users set username = 'chenliang' where username='chen1'";
string deleteSql = "delete from users where id = 2";

// exec = hp.Insert(insertSql, conn);
// exec = hp.Update(updateSql, conn);
exec = hp.Delete(deleteSql, conn);
if(exec){
Console.WriteLine("操作成功！");
}

Console.WriteLine("------ end ------");
Console.ReadLine();
}
// 可选参数
static void Check_User(string username,string pwd="123")
{
Console.WriteLine(" username:{0},password:{1}.",username,pwd);

}

// 连接数据库
public static void TestConn()
{
string connStr = "Data Source=.\\SQLEXPRESS;Database=MyStockDB;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(connStr);
if (conn != null)
{
Console.WriteLine("------ Connect SQLExpress Success! ------");
}
else
{
Console.WriteLine("------ Unknow ------");
}
}

public int MyProperty { get; set; }
}
}