& Quot; factory-like mode & quot; rewrite SqlHelper and sqlhelper

"Factory-like mode" rewrite SqlHelper and sqlhelper

You may be wondering when there will be an additional "factory-like model" for the 23 classic design models. Please let me know.

Practice is the only way to test truth. Recently, I used the "factory-like model" to rewrite my company's SqlHelper class. I changed more than half of the class and used semi-finished products to discuss with you.

First of all, let's talk about our company's Environment: Our company has factories in three places of ABC and their own databases. After investigation, many webpages in three places of ABC can be integrated. I am responsible for integrating webpages in three places.

In the first place, we were not in touch with the design model. My SQL statement is written as follows: "select * from" + strSite + ". dbo. table where Id = 'xxx '". the strSite in the SQL statement is the company type obtained from the URL.

In this way, the database of the ABC factory can be distinguished through strSite. In this way, I call the SqlHelper class: DBA. GetDataTable ("select * from" + strSite + ". dbo. Table where Id = 'xxx '");

There cannot be only one table in an SQL statement. If you write a table, you need to add "+ strSite +" to it. The logic of the Public page is complicated, and it is more troublesome to debug SQL statements. It is often because the system reports an error because it lacks the subsequent architecture or writes more database names.

Once you understand the error, you need to change it from the error. I found that strSite in SQL only provides an environment, for example, when strSite is, that is, the SQL statement is executed in Company A's DB. In Company B and Company C, the SQL Execution environment has changed.

Can I transmit a parameter to dynamically select different SQL environments during SqlConnection.

We did what we said, so we had the following code:

1 using System; 2 using System. collections. generic; 3 using System. data. sqlClient; 4 using System. linq; 5 using System. text; 6 using System. threading. tasks; 7 8 public class DBAStore 9 {10 private static string AConnection = "A company connection string"; 11 private static string BConnection = "B Company connection string "; 12 private static string CConnection = "C Company connection string"; 13 14 15 public SqlConnection OrderSqlConnection (string strSite) 16 {17 return GetConnection (strSite ); 18} 19 20 // obtain SqlConnection Method 21 22 private static SqlConnection GetConnection (string strSite) 23 {24 switch (strSite) 25 {26 case "": 27 return new SqlConnection (AConnection); 28 case "B": 29 return new SqlConnection (BConnection); 30 case "C": 31 return new SqlConnection (CConnection); 32 default: 33 return null; 34} 35} 36}DBAStore 1 using System; 2 using System. data; 3 using System. data. sqlClient; 4 using System. configuration; 5 using System. collections. generic; 6 using System. web; 7 using System. web. security; 8 using System. web. UI; 9 using System. web. UI. webControls; 10 using System. web. UI. webControls. webParts; 11 using System. web. UI. htmlControls; 12 using System. collections; 13 14 public class DBA15 {16 public DBA () 17 {18 // 19 // TODO: add constructor logic here20 // 21} 22 // <summary> 23 // execute the SQL statement to return the DataTable (new version) 24 /// </summary> 25 /// <param name = "strSite"> company </param> 26 /// <param name = "strSql"> SQLStatement </param> 27 // <returns> </returns> 28 public static DataTable GetDataTable (string strSite, string strSql) 29 {30 DBAStore dbaStore = new DBAStore (); 31 SqlConnection objConn = dbaStore. orderSqlConnection (strSite); 32 SqlDataAdapter objAdapter = new SqlDataAdapter (strSql, objConn); 33 try34 {35 DataSet ds = new DataSet (); 36 objAdapter. fill (ds, "dt"); 37 return ds. tables ["dt"]; 38} 39 catch (SqlException e) 40 {41 // write ErrorLog42} 43} 44 45 46}DBA

The above two classes can complete what I think, because they are written in Static mode, they can be used directly, so the following SQL statement is available: "select * from Table where Id = 'xxx'". If you want to obtain data from Company A, call the following command: DBA. getDataTable ("A", SqlStatemet); (SqlStatement is the SQL statement with A yellow background ).

With the modified SqlHelper, I showed off to my master. After reading it, the master frowned and said, It is good for you to write it like this, however, if you do not pass SQL helper into a company that is meaningless to the database, the coupling degree is increased. Although the current problem is solved, it is not good for future expansion. (In fact, this is not good for static classes. It is really impossible to fully support SqlHelper. If any of you have a good method, please do not mean to guide me)

Well, it is not good for the first time I leave the job. I briefly discussed the future architecture direction of SqlHelper with my master and our manager. I decided to stop using static classes to complete SQL statements on the integrated page, instead, we use the factory mode to write our SqlHelper.

In Head First, the factory model is interpreted using the Pizza example. There is a Pizza to decide how to make Pizza, and a PizzaStore to determine how to make Pizza in which region.

So I abstracted the change section of SqlHelper and wrote an abstract class SqlStatement, which contains GetConnection (get the connection string of different companies) and Result (get different return values, for example, DataTa or the first line) Two Abstract METHODS: SqlStatement determines how to execute an SQL statement, and write a DBAStore to determine the company's connection string.

The Code is as follows:

Similar to the abstract superclass of Pizza, how to execute SQL statements is implemented. DBAStore has the same functions as the class name. abstraction is used to determine the company's connection string to be obtained.

1 public abstract class SqlStatement 2 {3 public abstract SqlConnection GetSqlConnection (); 4 5 public abstract object Result (string strSql ); 6 // {7 /// SqlConnection sqlConnection = GetSqlConnection (strSite); 8 // return SqlCommand (strSql, get); 9 //} 10}SqlStatement 1 using System; 2 using System. collections. generic; 3 using System. data. sqlClient; 4 using System. linq; 5 using System. text; 6 using System. threading. tasks; 7 8 namespace DBHelp 9 {10 public abstract class DBAStore11 {12 public SqlConnection OrderDBA (string strSite) 13 {14 SqlConnection objConn; 15 objConn = CreateDBA (strSite); 16 return objConn; 17} 18 public abstract SqlConnection CreateDBA (string strSite); 19} 20 public class OA: DBAStore21 {22 private string _ aConnection = "Company A connection string "; 23 private string _ bConnection = "B Company connection string"; 24 private string _ cConnection = "C Company connection string "; 25 26 27 28 public string CConnection29 {30 get {return _ cConnection;} 31 set {_ cConnection = value ;} 32} 33 34 public string BConnection35 {36 get {return _ bConnection;} 37 set {_ bConnection = value ;} 38} 39 40 41 public string AConnection42 {43 get {return _ aConnection;} 44 set {_ aConnection = value;} 45} 46 47 public override SqlConnection CreateDBA (string strSite) 48 {49 switch (strSite) 50 {51 case "A": 52 return new SqlConnection (_ aConnection); 53 case "B": 54 return new SqlConnection (_ bConnection ); 55 case "C": 56 return new SqlConnection (_ cConnection); 57 default: 58 return null; 59} 60} 61} 62}DBAStore

GetDataTable class, inherited from SqlStatement, returns a DataTable type dt

1 using System; 2 using System. collections. generic; 3 using System. data; 4 using System. data. sqlClient; 5 using System. linq; 6 using System. text; 7 using System. threading. tasks; 8 9 namespace DBHelp10 {11 public class GetDataTable: SqlStatement12 {13 private string _ strSite; 14 15 public GetDataTable (string strSite) 16 {17 _ strSite = strSite; 18} 19 20 public override SqlConnection GetSqlConnection () 21 {22 DBAStore dbaStore = new OA (); 23 SqlConnection sqlConnection = dbaStore. orderDBA (_ strSite); 24 return sqlConnection; 25} 26 27 public override object Result (string strSql) 28 {29 SqlConnection sqlConnection = GetSqlConnection (); 30 SqlDataAdapter sqlDataAdapter = new SqlDataAdapter (strSql, sqlConnection); 31 sqlConnection. open (); 32 try33 {34 DataSet ds = new DataSet (); 35 sqlDataAdapter. fill (ds, "dt"); 36 return ds. tables ["dt"]; 37} 38 catch39 {40 return null; 41} 42 finally43 {44 sqlConnection. close (); 45} 46} 47} 48}GetDataTable

With the preceding three classes, we can execute SQL statements using the New instance and return the required values.

SqlStatement GetDataTable = new GetDataTable ("A"); // switch the database environment to Company A, just like using "use a" in SQL SERVER Management Studio.
GetDataTable. Result ("select Site from Table where Id in ('xxx', 'ooo')"); // execute the preceding yellow background SQL statement in Company.

If you need to return a Bool value to determine whether to execute the insert or delete statement, create an ExecSql statement, inherit from SqlStatement, and change the corresponding parameters to determine whether the execution is successful.

Because the code is easy to write, I will no longer provide the corresponding classes. You can try it on your own, which is really very simple.

 

However, the problem arises again. Every time I use different methods to execute the SQL statement, return the Bool value, or return a DataTable value, I have to get a New one each time, which is very complicated, can I directly use static methods like DBA ("A") as I did before "). getDataTable ("SQL statement") or DBA ("B "). execSql ("SQL statement"), dear friend, I am also working in this direction. I hope to discuss with you how to write a simpler SqlHelper.

 

At the end of the article, I will explain why I want to use the "factory-like model" to describe this SqlHelper. Do you find any clever friend, when I was writing the DBAStore class, I used a Switch to return the connection strings of different companies when creating a connection string, this is different from the one created in Head First. It is true that the Pizza store returns the Pizza type value, while the SqlStatement type is not returned in my DBAStore. This is a little different from the factory model. to commemorate my first understanding of the factory model, let me celebrate with this "factory-like model.

 

Conclusion:

Because the time is too late, I am also planning to send the blog post to my colleagues and ask them to give a thumbs up. The UML diagram will not be provided for the time being and will be provided later.

You may ask, why not provide a complete SqlHelper Method for your use? My explanation is that I have not completely achieved my dream. At the same time, when I wait for my QQ mailbox every day, I am very encouraged to see some comments from my previous article. If I do not update my blog post for a long time, they think I am missing, or I thought I gave up the programmer industry. For them, I chose to publish this semi-finished product for everyone to use.

Whether the code can be used or not: You can rest assured that I have been debugged and used in the formal environment of our company. It can be used only for the sake of confidentiality and security, there may be errors when I intercept the Code. If you Copy the Code but cannot use it, contact me for the complete code.

On the programmer's path: I have read Lee Kai-fu's autobiography "let the world be different from you". IT is really attracted by the work environment of IT giants such as Google and Microsoft, as well as the cool code they write. Back to reality, we graduated from the lowest-level university and won't go into code during college. Even if the president hopes that we can break out 20000 lines of code in four years, we don't even have 200 lines. After graduation, I hurried to find a training company for training, and then went to work, drag controls, repetitive work, and worked overtime for a long time. All the implementations crushed our ideal. But I want to say to you and myself that people should have dreams and make the world really different because of our existence. I hope that when you and I see this sentence, you and I will not use the old excuse to learn more and grow into a qualified programmer, rather than a code farmer or code animal.

 

There are a lot of things, a little too long, and the language is not clear and the logic is chaotic. Sorry.

Understanding of observer mode, Singleton mode, and factory-like Mode