Getting Started with Hyappframe database development (ASP. NET Web Service)

This section focuses on server-side ASP. NET WEB Service database configuration and operations, client database operations.

Hyappframe Project Address: https://sourceforge.net/u/chinahysoft/profile/

1. Server-side database operations
The Hyappframe server connects to the database through an ASP. NET Web service.

Database Connection Configuration
In the file Web. config database, you need to set the database server address data Source, database name initial Catalog, Access account user Id, access password password, and so on. DBName does not need to be modified.

<connectionStrings>    <add name= "dbName"      connectionstring= "Data source=localhost;initial catalog= Hyappframe;integrated Security=false; User Id=sa; password=123456; " /></connectionstrings>

Database Operations
Define the function OpenDatabase (string connstring) in file MyFuncLib.cs and return an active database connection SqlConnection with the following code:

public static SqlConnection OpenDatabase (String connstring) {    SqlConnection conn = new SqlConnection ();    Try    {        Conn. ConnectionString = connstring;        Conn. Open ();    }    catch (Exception ex)    {        Log ("Open Database error:" + ex. Message + "\ r \ n" + ex. StackTrace);    }    Return conn;}

database queries, update operations
Defines the function execquerybysql (String sql, DataTable paras) in file Master.asmx.cs, returns the SQL query result as a DataTable, and defines the function Execnonequerybysql ( String sql, DataTable paras), returns an int, 0 means no records are affected, 1 represents a SQL execution error, and >0 represents the number of bars that returned the affected record.

public int Execnonequerybysql (String sql, DataTable paras) {if (!    IsLogin ()) return-100;    SqlConnection conn = Myfunclib.opendatabase (myfunclib.connstring);    int result = 0; try {sql = DES.        Decrypt (SQL, Myfunclib.passwordkey);        SqlCommand comd = new SqlCommand ();        comd.commandtimeout = 1800; Comd.        Connection = conn;        Comd.commandtext = SQL; Comd.        Parameters.clear (); IF (paras! = null) {foreach (DataRow row in paras. Rows) {comd. Parameters.addwithvalue (MYFUNCLIB.DTV (Row, "name", String. Empty), Myfunclib.dtv (row, "value", String.)            Empty)); }} result = Comd.    ExecuteNonQuery (); } catch (Exception ex) {MyFuncLib.Log (ex. Message + "\ r \ n" + ex.        StackTrace);    result =-1;    } finally {myfunclib.closedatabase (conn); } return result;

To improve Web service security, the session is enabled, and the method must be authenticated first, and the SQL statement is des encrypted. The specific code is as follows:

[WebMethod (EnableSession = True)]public DataTable execquerybynamed (String queryname, DataTable paras)

2. Client Database Operations
The client uses the functions provided by the Web service to query and update the database.

Call the Web Service database action method directly, and construct the parameters required for the query.

DataTable sqlparams = new DataTable ("Sqlparametersdatatable"); SqlParams.Columns.Add (New DataColumn ("name", System.Type.GetType ("System.String")); SqlParams.Columns.Add (New DataColumn ("Value", System.Type.GetType (" System.String ")));D Atarow Drow;foreach (ListItem item in paras) {    drow = Sqlparams.newrow ();    drow["name"] = Item. Name;    drow["value"] = Item. Value;    SQLPARAMS.ROWS.ADD (drow);} DT = MYFUNCLIB.WS.EXECQUERYBYSQL (DES. Encrypt (SQL, Myfunclib.passwordkey), sqlparams);

For ease of invocation, encapsulate function Dbcommandexecquerybysql (String sql, ArrayList paras), Dbcommandexecscalarbysql (string MyFuncLib.cs) in client-side SQL, ArrayList paras), dbcommandexecnonequerybysql (String sql, ArrayList paras)

Queries with no parameters

String sql = "Select 1"; string str = myfunclib.dbcommandexecscalarbysql (sql, NULL);

Query with Parameters

String sql = "Select 1 from yourtable where id_ = @id"; ArrayList sqlparams = new ArrayList () Sqlparams.add (New ListItem ("@id", 1)); string str = Myfunclib.dbcommandexecscalarbysql (SQL, sqlparams);

after encapsulation, the database query code becomes very concise.

Getting Started with Hyappframe database development (ASP. NET Web Service)