SqlServerHelper. cs class for SqlServer database and its call example

UsingSystem; usingSystem. Collections. Generic; usingSystem. Linq; usingSystem. Text; usingSystem. Configuration; usingSystem. Data; usingSystem. Data. SqlClient; namespacedemo {detail {publicstaticstringConnStr

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 ConnStr

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 );}} // test the connection 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;} // obtain the public able 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 SQL DataAdapter (comm); dt = new DataTable (); dapter. fill (dt);} catch (Exception ex) {sError = ex. message;} return dt;} // get 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 d Apter = new SqlDataAdapter (comm); ds = new DataSet (); dapter. fill (ds);} catch (Exception ex) {sError = ex. message;} return ds;} // obtain a single 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;} // obtain the largest 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;} // execute the insert, update, and delete actions. You can also use the 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 // use the transaction {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 ;}}}

Call method:

1. Set database connection information first

// SqlServerHelper. ConnString = @ "server = computer name or computer IP address; database = database name; uid = database login name; password = database login password ";

SqlServerHelper. conn_Config_Str_Name = @ "ConnString"; // The ConnString information is stored in the App. set // SqlServerHelper in Config. conn_Server = @ "computer name or computer IP address"; // SqlServerHelper. conn_DBName = "Database Name"; // SqlServerHelper. conn_Uid = "database login name"; // SqlServerHelper. conn_Pwd = "database logon password ";

2. App. Config

3. Read datatable/dataset data

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 );

}

Iv. insert, modify, and delete data (all call the SqlServerHelper. UpdateData method)

// Insert

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, "inserted successfully"); else Common. displayMsg (this. text, sError );

InitGrid ();

// Modify

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, "modified successfully"); else Common. displayMsg (this. text, sError );

InitGrid ();

// Delete

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, "deleted successfully"); else Common. displayMsg (this. text, sError );

InitGrid ();

5. Others

Public static void DisplayMsg (string sCaption, string sMsg) {sMsg = sMsg. TrimEnd ('! '). TrimEnd ('! ') + "! "; MessageBox. Show (sMsg, sCaption );}