I'm learning C#, and, have created a class to simplify what I have to do to execute an SQL query and loop through the results and also, to run an update. In both cases, pass the SQL and an optional list of parameters.
Here's the class that does the simplification:
public db(string pQuerySQL, List<string> pParams=null) {
//Params array replace @PARAM1, @PARAM2, @PARAM3 (etc) in the SQL
//code would be: com.Parameters.Add(new OleDbParameter("@PARAM1", pParams[0]));
con= new OleDbConnection(db.conString);
com = con.CreateCommand();
com.CommandText = pQuerySQL;
addParamsToCom(lCom, pParams);
con.Open();
rs = com.ExecuteReader();
}
public static void runUpdate(string pUpdateSQL, List<string> pParams = null) {
//Params array replace @PARAM1, @PARAM2, @PARAM3 (etc) in the SQL
OleDbConnection lCon = new OleDbConnection(db.conString);
OleDbCommand lCom = lCon.CreateCommand();
lCom.CommandText=pUpdateSQL;
addParamsToCom(lCom, pParams);
lCon.Open();
lCom.ExecuteNonQuery();
lCon.Close();
}
private static void addParamsToCom(OleDbCommand pCom,List<string> pParams = null) {
if(!(pParams is null)) { //Add params to the SQL
for(int i = 0;i<pParams.Count;i++) {
pCom.Parameters.AddWithValue("@PARAM"+(i+1).ToString(), pParams[i]);
}
}
}
The advantage is that the class exposes the DataReader (recordset), so you can do db.rs.Read() or use any other functionality that it has.
Passing parameters is easy. Example with one parameter (to pass none, omit the 2nd parameter and just pass the SQL):
db db = new db(
"SELECT trackTitle FROM tracks WHERE [band]=@PARAM1",
new List<String> { "Sisters of Mercy" }
);
while (db.rs.Read()){
Debug.WriteLine(db.rs["trackTitle"]);
}
db.close();
[20171027: Added code to deal with parameters]
How could I make the connection close-itself as the object is destroyed (i.e. after its scope collapses)?
Copyright Notice:Content Author:「Vexen Crabtree」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/46832813/best-way-to-close-connection-in-a-class-to-simplify-c-sharp-db-connections-for-s