用于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 JonseTest { public 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); } } // 取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, "id", "test");
string sSql = "update test set name='name_jonse',remark='remark_jonse' where id=" + 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, "id", "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);
}