C# static Data Access Layer
hola,
this c# data access layer. example, cosider following 2 code snippets 1 using static class dataaccesslayer , other non-static class dataaccesslayer.
what pros , cons of static dal if gui asp.net or mvc page in web application. , if think question out of scope of forum, if gui windows form in dirstributed application?
please expert inputs.
code snippet 1:
//dal
using system; using system.configuration; using system.data.sqlclient; using system.data; static class dataaccesslayer { private static string connectionstring { { connectionstringsettingscollection connectionstringsettings = configurationmanager.connectionstrings; return connectionstringsettings["connectionstring"].connectionstring; } } public static datatable select(sqlcommand commandsql) { datatable dattable = null; try { using (sqlconnection connectionsql = new sqlconnection(connectionstring)) { commandsql.connection = connectionsql; commandsql.connection.open(); dattable = new datatable(); dattable.load(commandsql.executereader()); return dattable; } } { dattable = null; } } public static int execute(sqlcommand commandsql) { int rowsaffected = 0; using (sqlconnection connectionsql = new sqlconnection(connectionstring)) { commandsql.connection = connectionsql; commandsql.connection.open(); rowsaffected = commandsql.executenonquery(); } return rowsaffected; } } //gui using static class dataaccesslayer:
private datatable selectrecord() { datatable dattable = null; try { stringbuilder selectquery = new stringbuilder(); selectquery.append("select * customer;"); using (sqlcommand commandsql = new sqlcommand(selectquery.tostring())) { dattable = dataaccesslayer.select(commandsql); } } catch (exception ex) { messagebox.show(ex.tostring()); } return dattable; } private datatable selectrecord(string column) { datatable dattable = null; try { stringbuilder selectquery = new stringbuilder(); selectquery.append("select * customer "); if (!string.isnullorempty(textbox1.text.trim())) { selectquery.append("where "); selectquery.append("customerid = @customerid;"); } else if (!string.isnullorempty(textbox2.text.trim())) { selectquery.append("where "); selectquery.append("firstname = @firstname;"); } else if (!string.isnullorempty(textbox3.text.trim())) { selectquery.append("where "); selectquery.append("lastname = @lastname;"); } else if (!string.isnullorempty(textbox4.text.trim())) { selectquery.append("where "); selectquery.append("synonym = @synonym;"); } using (sqlcommand commandsql = new sqlcommand(selectquery.tostring())) { if (!string.isnullorempty(textbox1.text.trim())) { commandsql.parameters.addwithvalue("@customerid", convert.toint32(textbox1.text.trim())); } else if (!string.isnullorempty(textbox2.text.trim())) { commandsql.parameters.addwithvalue("@firstname", textbox2.text.trim()); } else if (!string.isnullorempty(textbox3.text.trim())) { commandsql.parameters.addwithvalue("@lastname", textbox3.text.trim()); } else if (!string.isnullorempty(textbox4.text.trim())) { commandsql.parameters.addwithvalue("@synonym", textbox4.text.trim()); } dattable = dataaccesslayer.select(commandsql); } } catch (exception ex) { messagebox.show(ex.tostring()); } return dattable; } private bool insert(string firstname, string lastname, string synonym) { bool isinserted = false; try { int rowsaffected = 0; stringbuilder insertquery = new stringbuilder(); insertquery.append("insert customer "); insertquery.append("("); insertquery.append("firstname, "); insertquery.append("lastname, "); insertquery.append("synonym "); insertquery.append(") "); insertquery.append("values "); insertquery.append("("); insertquery.append("@firstname, "); insertquery.append("@lastname, "); insertquery.append("@synonym "); insertquery.append(");"); using (sqlcommand commandsql = new sqlcommand(insertquery.tostring())) { commandsql.parameters.addwithvalue("@firstname", firstname); commandsql.parameters.addwithvalue("@lastname", lastname); commandsql.parameters.addwithvalue("@synonym", synonym); rowsaffected = dataaccesslayer.execute(commandsql); } if (rowsaffected > 0) { isinserted = true; } } catch (exception ex) { messagebox.show(ex.tostring()); } return isinserted; } private bool update(int custid, string firstname, string lastname, string synonym) { bool isupdated = false; try { int rowsaffected = 0; stringbuilder updatequery = new stringbuilder(); updatequery.append("update customer "); updatequery.append("set "); updatequery.append("firstname = @firstname, "); updatequery.append("lastname = @lastname, "); updatequery.append("synonym = @synonym "); updatequery.append("where "); updatequery.append("customerid = @customerid;"); using (sqlcommand commandsql = new sqlcommand(updatequery.tostring())) { commandsql.parameters.addwithvalue("@customerid", custid); commandsql.parameters.addwithvalue("@firstname", firstname); commandsql.parameters.addwithvalue("@lastname", lastname); commandsql.parameters.addwithvalue("@synonym", synonym); rowsaffected = dataaccesslayer.execute(commandsql); } if (rowsaffected > 0) { isupdated = true; } } catch (exception ex) { messagebox.show(ex.tostring()); } return isupdated; } private bool delete(int custid) { bool isdeleted = false; try { stringbuilder deletequery = new stringbuilder(); deletequery.append("delete customer "); deletequery.append("where "); deletequery.append("customerid = @customerid;"); sqlcommand commandsql = new sqlcommand(deletequery.tostring()); commandsql.parameters.addwithvalue("@customerid", custid); int rowsaffected = dataaccesslayer.execute(commandsql); if (rowsaffected > 0) { isdeleted = true; } } catch (exception ex) { messagebox.show(ex.tostring()); isdeleted = true; } return isdeleted; }
code snippet 2:
//dal
class dataaccesslayer { private string connectionstring { { connectionstringsettingscollection connectionstringsettings = configurationmanager.connectionstrings; return connectionstringsettings["connectionstring"].connectionstring; } } public datatable select(sqlcommand commandsql) { datatable dattable = null; try { using (sqlconnection connectionsql = new sqlconnection(connectionstring)) { commandsql.connection = connectionsql; commandsql.connection.open(); dattable = new datatable(); dattable.load(commandsql.executereader()); return dattable; } } { dattable = null; } } public int execute(sqlcommand commandsql) { int rowsaffected = 0; using (sqlconnection connectionsql = new sqlconnection(connectionstring)) { commandsql.connection = connectionsql; commandsql.connection.open(); rowsaffected = commandsql.executenonquery(); } return rowsaffected; } } //gui using class dataaccesslayer:
private datatable selectrecord() { datatable dattable = null; try { stringbuilder selectquery = new stringbuilder(); selectquery.append("select * customer;"); using (sqlcommand commandsql = new sqlcommand(selectquery.tostring())) { dataaccesslayer dal = new dataaccesslayer(); dattable = dal.select(commandsql); } } catch (exception ex) { messagebox.show(ex.tostring()); } return dattable; } private datatable selectrecord(string column) { datatable dattable = null; try { stringbuilder selectquery = new stringbuilder(); selectquery.append("select * customer "); if (!string.isnullorempty(textbox1.text.trim())) { selectquery.append("where "); selectquery.append("customerid = @customerid;"); } else if (!string.isnullorempty(textbox2.text.trim())) { selectquery.append("where "); selectquery.append("firstname = @firstname;"); } else if (!string.isnullorempty(textbox3.text.trim())) { selectquery.append("where "); selectquery.append("lastname = @lastname;"); } else if (!string.isnullorempty(textbox4.text.trim())) { selectquery.append("where "); selectquery.append("synonym = @synonym;"); } using (sqlcommand commandsql = new sqlcommand(selectquery.tostring())) { if (!string.isnullorempty(textbox1.text.trim())) { commandsql.parameters.addwithvalue("@customerid", convert.toint32(textbox1.text.trim())); } else if (!string.isnullorempty(textbox2.text.trim())) { commandsql.parameters.addwithvalue("@firstname", textbox2.text.trim()); } else if (!string.isnullorempty(textbox3.text.trim())) { commandsql.parameters.addwithvalue("@lastname", textbox3.text.trim()); } else if (!string.isnullorempty(textbox4.text.trim())) { commandsql.parameters.addwithvalue("@synonym", textbox4.text.trim()); } dataaccesslayer dal = new dataaccesslayer(); dattable = dal.select(commandsql); } } catch (exception ex) { messagebox.show(ex.tostring()); } return dattable; } private bool insert(string firstname, string lastname, string synonym) { bool isinserted = false; try { int rowsaffected = 0; stringbuilder insertquery = new stringbuilder(); insertquery.append("insert customer "); insertquery.append("("); insertquery.append("firstname, "); insertquery.append("lastname, "); insertquery.append("synonym "); insertquery.append(") "); insertquery.append("values "); insertquery.append("("); insertquery.append("@firstname, "); insertquery.append("@lastname, "); insertquery.append("@synonym "); insertquery.append(");"); using (sqlcommand commandsql = new sqlcommand(insertquery.tostring())) { commandsql.parameters.addwithvalue("@firstname", firstname); commandsql.parameters.addwithvalue("@lastname", lastname); commandsql.parameters.addwithvalue("@synonym", synonym); dataaccesslayer dal = new dataaccesslayer(); rowsaffected = dal.execute(commandsql); } if (rowsaffected > 0) { isinserted = true; } } catch (exception ex) { messagebox.show(ex.tostring()); } return isinserted; } private bool update(int custid, string firstname, string lastname, string synonym) { bool isupdated = false; try { int rowsaffected = 0; stringbuilder updatequery = new stringbuilder(); updatequery.append("update customer "); updatequery.append("set "); updatequery.append("firstname = @firstname, "); updatequery.append("lastname = @lastname, "); updatequery.append("synonym = @synonym "); updatequery.append("where "); updatequery.append("customerid = @customerid;"); using (sqlcommand commandsql = new sqlcommand(updatequery.tostring())) { commandsql.parameters.addwithvalue("@customerid", custid); commandsql.parameters.addwithvalue("@firstname", firstname); commandsql.parameters.addwithvalue("@lastname", lastname); commandsql.parameters.addwithvalue("@synonym", synonym); dataaccesslayer dal = new dataaccesslayer(); rowsaffected = dal.execute(commandsql); } if (rowsaffected > 0) { isupdated = true; } } catch (exception ex) { messagebox.show(ex.tostring()); } return isupdated; } private bool delete(int custid) { bool isdeleted = false; try { stringbuilder deletequery = new stringbuilder(); deletequery.append("delete customer "); deletequery.append("where "); deletequery.append("customerid = @customerid;"); sqlcommand commandsql = new sqlcommand(deletequery.tostring()); commandsql.parameters.addwithvalue("@customerid", custid); dataaccesslayer dal = new dataaccesslayer(); int rowsaffected = dal.execute(commandsql); if (rowsaffected > 0) { isdeleted = true; } } catch (exception ex) { messagebox.show(ex.tostring()); isdeleted = true; } return isdeleted; } thanks
"when use static classes or singleton, 1 thread ever access database @ once, avoiding race conditions. "
false. using static class doesn't imply nor require 1 thread accesses database.
"what data access web application viz., asp.net web form or mvc page, static fine in web application?"
it's irrelevant kind of application is, static classes works same way in kind of application.
"what dirty read or concurrency issues?"
those database issues , such need handled on database side. makes no difference if dal static or not.
the problem static classes they're less flexible. ok, wrote dataaccesslayer class talks sql server.
now if want oracle dal? can create static class how make rest of code use it? search , replace "dataaccesslayer." "oracledataaccesslayer."? doesn't fly.
when use non static dal can use factory creates appropriate dal instance depending on configuration. can switch between various types of dal without changing code.
that said, you're example dal simplistic using static class or not doesn't make difference. you're creating sqlcommand objects outside of dal can't switch between different types of dal if dal non static. frankly wouldn't call class dal, "sqlconnectionhelper" more suitable name :)
Visual Studio Languages , .NET Framework > Visual C#
Comments
Post a Comment