Introduction
This project builds up a program which takes a list of all databases included in the given data source (server) and besides takes another list of tables within the given database name; then generates a class abstraction layer for that table. The generated class takes the same name with the table. And if desired, that class representation can be saved as a .cs source code file.
Code Explanation
As you can see aside, the solution includes a class library project and a test form project which is the start up project. The test form includes two forms: TestMain.cs and Input.cs. Input form gets datasource that is the server name(often '.') for trusted connection. Or it gets servername, userid, password entries for authenticated login. Then TestForm takes the control and if input is valid, it paints on the screen. Else the application exits.
In sqlclassfactory class library project, you can see different classes. All of these classes are declared as static. The start point is Constants class. This class includes strings like connection strings that aren't subject to being changed. ConnStrArgs.cs includes a static class named Args. This class is critical. Because it is the class which must be set before making other operations with the database. All other static classes get server information from this class. As shown below, it must be set first:
SqlClassFactory.Args.Reset();
SqlClassFactory.Args.Type = SqlClassFactory.enmConnStrType.Trusted;
SqlClassFactory.Args.DataSource = dataSource;
SqlClassFactory.Args.InitialCatalog = "master";
The next class is Connections.cs class. This class has a private field named _connection with type of SqlConnection. And it includes a static method named GetConnection(). This method takes the format string from Constants and takes the parameters into that format string from Args class and builds a new connection and returns it. As you can see each time that method calls a new connection with Args which could be set again and again.
Commands class is only a host class for raw SqlCommands. The commands' CommandText and Connection properties are set in Adapters. In Adapters class, these properties are set like:
Commands.ListDBCmd.CommandText = Constants.ListDBStr;
Commands.ListDBCmd.Connection = Connections.GetConnection();
Adapters._serverDA = new SqlDataAdapter(Commands.ListDBCmd);
For example, here ListDBCmd command is set and ServerDA property is set to new SqlDataAdapter with this command.
The last class is Generator Class. This static class includes a method named BeginGeneration(string). Like a constructor, this method takes a string parameter as rawClassText and internalizes it. I truly explain it in "Using The Code" section.
The "Class.txt"
Class.txt is the file in which rawClassText is included. rawClassText is the string used to generate table classes from specified database. Some fields of Class.txt are special-<...> fields. These fields are substituted with their matches like propertyNames or className.
public DataTable DoSelect(string SqlCommandText)
{
SqlCommand selectCommand = new SqlCommand(SqlCommandText);
selectCommand.Connection = sqlConnection;
sqlDA.SelectCommand = selectCommand;
DataTable retdt = new DataTable();
sqlDA.Fill(retdt);
return retdt;
}
Here you see DoSelect() method of "Class.txt". Here sqlDA is class member and initialized in the constructor. This method is the simplest method in the class. It fills retdt and returns it.
public bool DoInsert(string SqlCommandText, params object[] args)
{
SqlCommand insertCommand = new SqlCommand(SqlCommandText);
string argInCmd = null;
Regex r = new Regex(@"@(\S+)", RegexOptions.IgnoreCase);
int i = 0;
if (r.Matches(SqlCommandText).Count == args.Length)
{
foreach (object arg in args)
{
argInCmd = r.Matches(SqlCommandText)[i].ToString();
argInCmd = argInCmd.Trim().TrimEnd(new char[] {')', ','});
insertCommand.Parameters.AddWithValue(argInCmd, arg);
i++;
}
}
else
return false;
insertCommand.Connection = sqlConnection;
sqlConnection.Open();
bool ret = insertCommand.ExecuteNonQuery() > 0;
sqlConnection.Close();
return ret;
}
Here you see DoInsert() method of "Class.txt". This method is somewhat more complex. It is used so:
DoInsert("Insert into Employees (firstname, lastname) values (@fname, @lname)", _
"Ozgur", "Sonmez");
It is the smartest method of all the entire project. It takes a params argument args. This argument covers parameters which are to be added to the sqlcommand. argInCmd string is '@fname' firstly and '@lname' secondly in the upper example. argInCmd is exactly the match for arg in turn.
new Regex(@"@(\S+)", RegexOptions.IgnoreCase);
is the match for '@matched_parameter'. After this is taken with <r.Matches(SqlCommandText)[i].ToString();>, there comes the trim stuff. For some reason, I couldn't make C# find the pure match. So some matches come with '@lname)' or '@fname,' so I made this trim job. And now pure match string for the inner parameter substring is in argInCmd(argument in Command). It's added to parameters of sqlcommand with Parameter.AddWithValue() method and it is up to opening the connection, executing the nonquery and so on.
Using the Code
This source code includes a class library and a test form. For brevity, I used two separate listbox controls to view database and related tables list which comes from SqlClassFactory DLL. The class library is SqlClassFactory.dll and the main entrance is TestForm.exe. If you desire truly you can use class library to get two lists and use them anywhere. This is not the only work SqlClassLibrary class does. It also includes a generic <Generator> class which includes <public static void BeginGeneration(string rawText)>. This method takes rawText string from Class.txt text file and replaces all <...>s with appropriate names. Here is an example part of Class.txt:
namespace <database_name>
{ public class <table_name>
{ <column_name>
SqlDataAdapter sqlDA = null;
SqlConnection sqlConnection = null;
Below there is the core of the TestForm application:
SqlClassFactory.Generator.BeginGeneration(rtxtClass.Text);
SqlClassFactory.Generator.AddTableDBName(tableName, dbName);
foreach (DataRow row in dtColumns.Rows)
{
SqlClassFactory.Generator.AddField
(row["DATA_TYPE"].ToString(), row["COLUMN_NAME"].ToString());
}
SqlClassFactory.Generator.EndGeneration();
rtxtClass.Text = SqlClassFactory.Generator.RawClassText;
Here BeginGenerator() static method takes Text data from richtextbox rtxtClass and internalizes it into SqlClassFactory.Generator class. Then Generator.AddTableDBName(..) method replaces "<database_name>" and "<table_name>" -within the rtxtClass.Text string - with appropriate names. Then a foreach loop with AddField() method of Generator Static Class each time is used to add Property names to raw class string which had come from Class.txt. This method replaces "<column_name>" with the property name inturn - that is the column name of the given table in the given database- as you can get from given parameters above:
row["DATA_TYPE"].ToString(), row["COLUMN_NAME"].ToString()
This function deserves interest. Within this function, there are the critical code rows as below:
string fieldText =
String.Format("public {0} {1}", propertyType, propertyName)
+ " { get; set; }\n\t\t\t<column_name>";
RawClassText = RawClassText.Replace("<column_name>", fieldText);
Here, each time the AddField() calls fieldtext is set to "public int productId { get; set; } <column_name>" like string for the first place property type and second for the property name and column_name sign for the next AddField() call as when called will set new line for new property and add the second newline "<column_name>".
SqlClassFactory.Generator.EndGeneration();
This row makes replacement of "<column_name>" string with String.Empty that erases the excess.
rtxtClass.Text = SqlClassFactory.Generator.RawClassText;
By that row of code, layout string is changed with result string.
Points of Interest
I learned column names table can also be set up of rows. :)
History
This is the first version yet.
