用于SqlServer数据库的SqlServerHelper.cs类，及其调用例子
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace demo
{
public abstract class SqlServerHelper
{
public static string ConnString = string.Empty;
public static string Conn_Config_Str_Name = string.Empty;
public static string Conn_Server = string.Empty;
public static string Conn_DBName = string.Empty;
public static string Conn_Uid = string.Empty;
public static string Conn_Pwd = string.Empty;
private static string _ConnString
{
get
{
if (!string.IsNullOrEmpty(ConnString))
return ConnString;
object oConn = ConfigurationManager.ConnectionStrings[Conn_Config_Str_Name];
if (oConn != null && oConn.ToString() != "")
return oConn.ToString();
return string.Format(@"server={0};database={1};uid={2};password={3}",Conn_Server,Conn_DBName,Conn_Uid,Conn_Pwd);
}
}
// 测试连接
public static bool TestConn()
{
SqlConnection myConn = null;
bool bResult = false;
try
{
myConn = new SqlConnection(_ConnString);
myConn.Open();
}
catch (Exception ex)
{
}
finally
{
if (myConn != null && myConn.State.ToString() == "Open")
bResult = true;
}
myConn.Close();
return bResult;
}
// 取datatable
public static DataTable GetDataTable(out string sError,string sSQL)
{
DataTable dt = null;
sError = string.Empty;
try
{
SqlConnection conn = new SqlConnection(_ConnString);
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sSQL;
SqlDataAdapter dapter = new SqlDataAdapter(comm);
dt = new DataTable();
dapter.Fill(dt);
}
catch (Exception ex)
{
sError = ex.Message;
}
return dt;
}
// 取dataset
public static DataSet GetDataSet(out string sError,string sSQL)
{
DataSet ds = null;
sError = string.Empty;
try
{
SqlConnection conn = new SqlConnection(_ConnString);
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sSQL;
SqlDataAdapter dapter = new SqlDataAdapter(comm);
ds = new DataSet();
dapter.Fill(ds);
}
catch (Exception ex)
{
sError = ex.Message;
}
return ds;
}
// 取某个单一的元素
public static object GetSingle(out string sError,string sSQL)
{
DataTable dt = GetDataTable(out sError,sSQL);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
// 取最大的ID
public static Int32 GetMaxID(out string sError,string sKeyField,string sTableName)
{
DataTable dt = GetDataTable(out sError,"select isnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]");
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
// 执行 insert,update,delete 动作，也可以使用事务
public static bool UpdateData(out string sError,string sSQL,bool bUseTransaction = false)
{
int iResult = 0;
sError = string.Empty;
if (!bUseTransaction)
{
try
{
SqlConnection conn = new SqlConnection(_ConnString);
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sSQL;
iResult = comm.ExecuteNonQuery();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
SqlTransaction trans = null;
try
{
SqlConnection conn = new SqlConnection(_ConnString);
if (conn.State != ConnectionState.Open)
conn.Open();
trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
cmd.Transaction = trans;
iResult = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
trans.Rollback();
}
}
return iResult > 0;
}
}
}
调用方法： ? 一，先设置数据库连接的信息 ????????????//SqlServerHelper.ConnString = @"server=电脑名 或 电脑IP;database=数据库名;uid=数据库登录名;password=数据库登录密码"; ??????????? SqlServerHelper.Conn_Config_Str_Name = @"ConnString";? // ConnString的信息在 App.Config里设置???????????????????????? //SqlServerHelper.Conn_Server = @"电脑名 或 电脑IP";??????????? //SqlServerHelper.Conn_DBName = "数据库名";??????????? //SqlServerHelper.Conn_Uid = "数据库登录名";??????????? //SqlServerHelper.Conn_Pwd = "数据库登录密码"; ? 二， App.Config ? <?xml version="1.0" encoding="utf-8" ?><configuration>? <connectionStrings>??? <add name="ConnString" connectionString="server=电脑名 或 电脑IP;database=数据库名;uid=数据库登录名;password=数据库登录密码" />? </connectionStrings></configuration> ? 三,? 读取 datatable / dataset 数据 ?????????? private void InitGrid()?????????? { ??????????? string sSQL = "select * from test"; ??????????? string sError = string.Empty; ??????????? DataTable dt = SqlServerHelper.GetDataTable(out sError,sSQL); ??????????? //DataSet dt = SqlServerHelper.GetDataSet(out sError,sSQL); ??????????? dataGridView1.DataSource = dt; ??????????? if (!string.IsNullOrEmpty(sError))??????????????? Common.DisplayMsg(this.Text,sError); ?????????? } ? 四，插入，修改，删除 数据?（都调用SqlServerHelper.UpdateData方法） ??????????? // 插入 ??????????? string? sError = string.Empty;??????????? int iMaxID = SqlServerHelper.GetMaxID(out sError,"id","test") + 1;??????????? string sSql = "insert into test select " + iMaxID + ",'name" + iMaxID + "','remark" + iMaxID + "'";??????????? sError = string.Empty;??????????? bool bResult = SqlServerHelper.UpdateData(out sError,sSql,true);??????????? if (bResult)??????????????? Common.DisplayMsg(this.Text,"插入成功");??????????? else??????????????? Common.DisplayMsg(this.Text,sError); ??????????? InitGrid(); ? ??????????? // 修改 ??????????? sError = string.Empty;??????????? int iMaxID = SqlServerHelper.GetMaxID(out sError,"test");??????????? string sSql = "update test set name='name_jonse',remark='remark_jonse' where id=" + iMaxID;??????????? sError = string.Empty;??????????? bool bResult = SqlServerHelper.UpdateData(out sError,"修改成功");??????????? else??????????????? Common.DisplayMsg(this.Text,sError); ??????????? InitGrid(); ? ???????????? // 删除 ??????????? sError = string.Empty;??????????? int iMaxID = SqlServerHelper.GetMaxID(out sError,"test");??????????? string sSql = "delete from test where id=" + iMaxID;??????????? sError = string.Empty;??????????? bool bResult = SqlServerHelper.UpdateData(out sError,sSql);??????????? if (bResult)??????????????? Common.DisplayMsg(this.Text,"删除成功");??????????? else??????????????? Common.DisplayMsg(this.Text,sError); ??????????? InitGrid(); ? 五，其它 ? ?????? public static void DisplayMsg(string sCaption,string sMsg)?????? {?????????? sMsg = sMsg.TrimEnd('!').TrimEnd('！') + " !";?????????? MessageBox.Show(sMsg,sCaption);?????? } ?
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
