posted
A number of months ago (perhaps even a year) I had mentioned that I was trying to find a way to automatically generate Create, Read,Update,Delete stored procedures based on database schemas.
For the most part, this is tediously repititious work that, while simple, can also be very prone to error. Throw in 60 tables with 5 columns each, and it becomes a nightmare.
At the time a few people (including Mr. Squicky) gave some suggestions on how to automate the process- a java program, and excel spreadsheet. The project got put on the backburner, however, and I never had to use it.
Well the occasion came up a again a week ago. And I decided to just design a small app to do it. Stop wasting time looking for exactly what I want and make it.
I include both because, while I generate SQL code in a format I like, that may not be how you like it. I suppose if I was looking for real custimizability, I'd create a stored procedure 'template' in XML, and then just read that in and generate according to that. But this is good enough for what I need it to do. But anyone who'd like to fool with it can.
A couple of notes/caveats: 1) It allows you select SQLserver databases only, and those have to be running on your development machine. It doesn't allow you to look up all DB servers in your network and then select a DB on a particular server (mainly cause I don't know how to query for the list of DB servers out there.) I suppose I could adapt it to include MySQL dbs later.
2) The tables are assumed named according to the convention "****Table". The 'root' becomes "****.
3) The CREATE SP returns an integer with the ID of the newly created record, 'root'+ID+'out'. It is assumed that this value is "autoincremented" .Therefore, 'keyIDout'.
4)There should be a primary key DEFINED for each table. That is to say, the property for that column should actually indicate that it is a primary key. Now, there is a bit of logic checking for when a key is not explicity defined. Primarily, if there is no column with "Primary Key" attribute set, it will check to see if a column name exists with the name "****ID", where "****" is the root above. So, for example, I have a table, "keyTable". If I had no primary key explicitly defined, it will look for "keyID" and use that as the primary key.
If the table doesn't have a primary key, then the generated SQL will be a bit different. Clearly, DELETE and UPDATE will not be possible, since these depend on ID's (unless I wanted to do a test of all/certain values in the table, which I didn't). And SELECTing by ID of records will not be possible. So all that will be generated is a general READ (SELECT * FROM ****Table) and a CREATE which WILL NOT return a primary key value. The only place that should be potential problem are "many-to-many" tables that relate keys from one table to keys in another table.
5) The created stored procedures follow the convention: sp_[tableroot]ADD/DELETE/(S)GET/UDPATE. The (S)GET is for mutiple and specific SELECT statements. So we have sp_keySGET and sp_keyGET. One for getting ALL records and one for getting a specific record.
A log is created indicating what could be generated and what couldn't.
Once created and reviewed, the stored procedures can be automatically written to the Database.
This tool is meant to help, but clearly there might be some tweaking needed of the generated SQL. That's why I include the project.
I hope it helps. And let me know of any problems. I tried to anticipate any problems, but obviously, there are some DB datatypes, for example, that I might not generate the correct SQL for. Just let me know: email: i no space ohlander at ogequip dot com.
posted
Okay, I didn't understand most of what you said, but because it's the middle of the night with insomnia and I think I need to make sure Fahim sees this in the morning, I'm going to bump this.
posted
I have made quite a few changes to this application. It now not only generates Stored Procedures based on a db schema, but also the entire basic data layer.
This is from the documentation, quoted to preserve formatting.
Please let me know what you think or if you have any suggestions or see any bugs.
quote:This applications purpose is to Automatically generate standard Stored Procedures and the entire Data Layer (using Model-View-Controller architecture) for an application. This application is ONLY for use with SQL Server databases. The Data Layer code is generated in C#.
For the most part, this is tediously repititious work that, while simple, can also be very prone to error. Throw in 60 tables with 5 columns each, and it becomes a nightmare.
This application requires the .NET framework (1.1, at least. I haven't checked 2.0 compatibility, though I don't imagine it would be problem.
A couple of notes/caveats:
General: 1) It allows you select SQLserver databases only, and those have to be running on your development machine. It doesn't allow you to look up all DB servers in your network and then select a DB on a particular server (mainly cause I don't know how to query for the list of DB servers out there.)
2) The tables are assumed named according to the convention "****Table". The 'root' becomes "****.
Stored Procedures 3) The CREATE SP returns an integer with the ID of the newly created record using the variable 'root'+ID+'out'. It is assumed that this value is "autoincremented" .Therefore, 'keyIDout'.
4)There should be a primary key DEFINED for each table. That is to say, the property for that column should actually indicate that it is a primary key. Now, there is a bit of logic checking for when a primary key is not explicity defined. Primarily, if there is no column with "Primary Key" attribute set, it will check to see if a column name exists with the name "****ID", where "****" is the root above. So, for example, I have a table, "keyTable". If I had no primary key explicitly defined, it will look for "keyID" and use that as the primary key.
If the table doesn't have a primary key, then the generated Stored Procedures will be a bit different. Clearly, DELETE and UPDATE will not be possible, since these depend on ID's (unless I wanted to do a test of all/certain values in the table, which I didn't). And SELECTing a record by ID will also not be possible. So all that will be generated is a general READ (SELECT * FROM ****Table) and a CREATE which WILL NOT return a primary key value. The only place that should be potential problem are "many-to-many" tables that relate keys from one table to keys in another table.
5) The created stored procedures follow the convention: sp_[tableroot]ADD/DELETE/(S)GET/UDPATE. The (S)GET is for mutiple and specific SELECT statements. So we have sp_keySGET and sp_keyGET. One for getting ALL records and one for getting a specific record.
6) A log is created indicating what could be generated and what couldn't.
Once created and reviewed, the stored procedures can be automatically written to the Database.
Data Layer 7) This Generated Code is designed to represent and communicate data from the database. It is designed to be the Model layer of the Model-View-Controller architecture. Ideally, if all caveats are adhered to, creating an application should be greatly simplified. Especially if Microsoft Data Application Blocks are used, a developer can focus primarily on the Business (Controller) and Presentation (View) layers of an application. Use of Visio (or some other visual DB designing tool) increases ease of use. Once a database is created, the application will generate all standard communication tools for that database based on it's schema.
8) As mentioned, all Data Layer Code is generated in C#. There are many utilities on the web that can convert C# to VB.NET, if you so desire.
Data Objects 9) Data Objects represent a row or record in a database table. These DataObjects follow the convention "****Object" where **** is the table name. So records from "administratorTable" will be represented by "administratorTableObject".
10) These objects can be used in any layer of the application and are very useful for adding and getting records from the database.
11) These objects contain one property for each field. I have included support for the following SQL Server datatypes: varchar->string int->int bit->bool binary->Byte[] decimal->decimal datetime->DateTime
The defintion (and necessary information) for each of these datatypes is stored in the XML file dataTypeConvertInfo.xml. Here is an example: ...<dataTypeConvertInfo> ......<DBDataType>int</DBDataType> ......<systemDataType>System.Int32</systemDataType> ......<dataTypeString>SqlDbType.Int</dataTypeString> ......<convertString>System.Convert.ToInt32(</convertString> ......<dummyData>3</dummyData> ......<dummyData2>2</dummyData2> ......<columnLengthSpecified>no</columnLengthSpecified> ...</dataTypeConvertInfo> The tags are defined as follows:
DBDataType: the datatype as defined in SQL Server systemDataType: the datatype as it would be defined in the .NET framework. For example, int, string, DateTime, Byte[], etc dataTypeString: The SqlDbType enumeration. Necessary in defining parameters for stored procedures. convertString: The necessary string to convert the information in the retrieved DB field to a proper .NET datatype. Note that binary, for example, uses the following: new System.Text.ASCIIEncoding().GetBytes( dummyData: For use in the test harness. Data that is of the appropriate type. Strings MUST have quotation marks around them. dummyData2: For use in the test harness, for updating a record with different data than originally there. Restrictions above also apply. columnLengthSpecified: In .NET, some retrieved DataTable columns will indicate column length. Others wont. String will, for example. This property (yes/no) will determine if an attempt will be made to get the defined column length. DB datatype binary, for example, will not return a value, despite defining one in the DB schema. If set to no, you can indicate the column length by placing it in the DBDataType tag. For example, binary(10), in the case of binary.
If properly defined in this XML file, the SQL Server datatypes supported can be expanded to include all it uses.
If not properly defined,an error will be caused during the execution of the Crud & Data Layer Generation application.
Data Utilities 12) Data Utilites are responsible for actually communicating with the database. The Utilities are created using the following inheritance pattern (administratorTable and roleTable as sample tables in the DB): ..................dbUtilityObject ..................______|_______ ...................|...................... | administratorTableUtility.....roleTableUtility
dbUtilityObject contains the actual code for communicating with the DB. The following methods are defined for the dbUtilityObject:
public DataSet getXXXDataSet(string spName) public SqlDataReader getXXXDataReader(String spName) public void getXXXDataReader(String spName, ref SqlDataReader dr) public DataSet getXXXDataSet(String spName, SqlParameter[] parms) public void getXXXDataSet(String spName, DataSet ds) public bool addXXX(String spName, SqlParameter[] parms) public bool updateXXX(String spName, SqlParameter[] parms) public bool deleteXXX(String spName, SqlParameter[] parms) public bool changeXXX(String spName, SqlParameter[] parms)
You have the option of using Microsoft Data Application Blocks (which can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=f63d1f0a-9877-4a7b-88ec-0426b48df275&DisplayLang=en unless MS changes its links) to actually communicate with the DB. If the "Use MS Data Appliation Blocks" checkbox is selected, then the code will be completely functional, PROVIDED YOU INCLUDE A REFERENCE TO THE Microsoft.ApplicationBlocks.Data.dll when compiling (and, of course, your connection string is valid). If the check box is not selected, stub methods will be created in dbUtilityObject for communicating with the DB.
13) The connection string is actually defined in the dbUtilityObject class. This is, strictly speaking, not a best practice. Connection strings should be defined outside the actual application in a config file. However, for initial creation and testing purposes, this method works. Just be aware that in a production environment, the connection string should be read in from a config file.
14) Each child Utility object then exposes the following interface (using administratorTableUtility from above):
public DataSet get_administratorS() public void get_administratorS(DataSet ds) public SqlDataReader get_administratorSReader() public void get_administratorSReader(ref SqlDataReader dr) public bool delete_administrator(System.Int32 administratorID) public administratorTableObject get_administrator(System.Int32 administratorID) public bool add_administrator(administratorTableObject obj) public bool update_administrator(administratorTableObject obj)
This interface is unique to the administratorTable. Aside from the obvious method names, add_administrator , update_administrator , and get_administrator all use the administratorTableObject to pass information between the underlying database and all subsequent layers. Note, too, that the get method takes a primary key value. This value, as mentioned in the first section, is based on the underlying schema of which column is the primary key and what datatype it takes.It returns an administratorTableObject fully populated with data from the DB. Obviously, tables which have no Primary Key will have utilities that are missing the update, get, and delete methods and whose add method will return a Data Object with no value for the Primary Key property.
Each method constructs the necessary parameters list and then calls the underlying dbUtilityObject generic method. The get method also reads in the data and populates the Data Object and returns that (or null, if there was no match).
15) Extending this class is fairly easy. Assuming you have created new stored procedures (non-standard), you can then create the appropriate method in the Utility class. In this method, merely build your parameter array list (using the other methods as a model) for both passed and returned variables. Then call the appropriate dbUtilityObject method.
Test Harness 16) The CRUD and Data Layer Creation Tool will also generate a test harness called testDataLayer. This class has one method test(bool web) that returns a string with the output of the test. The argument, bool web, is used to indicate whether the returned string is for display on a web page or in an application. (This primarily applies to indenting, using either "\t" or " ", for formatting.)
17) test returns a string with the output of the following tests: -creation of all generated data objects -population of generated data objects using dummy data (defined in dataTypeConvertInfo.xml. See 11) -creation of all utility objects -testing all add and get methods in each utility object, using generated data objects -testing update method using data objects populated with some new dummy data (defined in dataTypeConvertInfo.xml. See 11) -testing all DataSet and SqlDataReader Get methods in each utility object.
18) The returned string might be displayed in a web page Label or in a windows application TextBox (for example)
19) One note: if a field is a reference to a Foreign Key in another table, and if Foreign Key (FK) constaints apply to that field, the Foreign Table will be checked to see if a Primary Key exists with that value. For example, if your FK is an int ID on another table and is "2", then that other table will be checked to see if there is a record with the Primary Key of "2". This can be a potential place for errors in the test harness. I have tried to get around this by running all the test code for tables with NO Foreign Keys first. Then the tests for tables with Foreign Keys are run. Hopefully, this ensures that the independent table gets data first, and then the dependent ones. Just be aware. You may have to changed the dummydata field in the dataTypeConvertInfo.xml file.
An Example 20) a) Let's say you have a sample database with this schema.
b) The CRUD & Data Layer Generation Tool will automatically generate the following Stored Procedures (with the necessary parameters): sp_test1ADD sp_test2ADD sp_test3ADD sp_test1DELETE sp_test2DELETE sp_test1GET sp_test2GET sp_test1SGET sp_test2SGET sp_test3SGET sp_test1UPDATE sp_test2UPDATE
c) Then, after you have entered the appropriate information (package, author name), the following objects are created: test1TableObject.cs test2TableObject.cs test3TableObject.cs
These objects will have properties that match those of the database tables. These files can be save, in bulk, to whatever folder location you'd like. In this case, I have created a folder called "testCrudApp". Under that folder, I created a "dataObject" folder and saved these object there.
d) Finally, after entering the package and connection string and selecting "Use Microsoft Data Application Blocks", the following utilities and classes are created:
Again, these utility objects will be unique to the underlying DB structure, as will the testDataLayer.
e) Create an application (web or windows) either by hand or in Visual Studio.NET 2003 or 2005. (Again, I haven't tested .NET 2.0, but I don't think it shouldn't work.) Add references to the packages for both the Data Objects and Data Utilities.
f) If using Visual Studio, add a reference to the "Microsoft.ApplicationBlocks.Data.dll" downloaded from Microsoft's website (or from the zip file.) g) In the application, create an instance of the "testDataLayer" object and call the test method (indicating whether the returned string is for web or windows application display.) h) Display the returned string in the application, using a Label or TextBox (or whatever you desire.) i) Compile the C# project. If compiling by hand, be sure to add a reference to the "Microsoft.ApplicationBlocks.Data.dll" downloaded from Microsoft's website (or from the zip file.) j) Run the project. You should see the detailed status of all the tests run. Assuming things like the connection string and DB permissions are set properly, every test should run successfully and this will be indicated in the output. Checking the DB itself, you should see the test data added to the DB.
The entire Data Layer has been generated entirely based on the underlying database schema.
Please let me know if there are any bugs or suggestions.
posted
OK, made a few slight changes to the overall app as I have used it in conjuction with a project I am developing.
1) got rid of the "change_xxx()" method in dbUtilityObject. This is redundant with "update_xxx()" already a method.
2)Added getDatabase() to the dbUtilityObject. This is the only method that is not created in (or called from) the child utility classes. This method returns a DataSet with the entire database.
3) Made a change to the way xxxObjects are populated from the database. This is used in conjunction with the dataTypeConvertInfo.xml file. Prior to this, the code generated populating the object followed this model:
This worked for most of the supported datatypes. But not for Byte/Binary. The actual way to pull the data out and populate the object is a simple cast, not using the ASCIIEncoding.GetByte() method.
Thus, the new pattern, which works for ALL datatypes, now, is this.
The primary difference is the missing ToString() previously called on the return DB object. The problem was that this returned the System datatype name, not the actual data, in the case of Binary. The XML file was slightly adjusted to be this:
(byte[])( which is a simple casting. Thus, a generated example is this: obj.administratorPassword=(byte[])(administratorTable.Rows[0]["administratorPassword"]);
posted
I recently installed and started using SQL Server Express 2005 (download free here. BTW, I really like using it.) So I needed to update my CRUD & DL Creator Application for use with SQLExpress.
The updated program can be downloaded at the same links as above. As before, both the Visual Studio 2003 solution as well as the actual stand alone app are available.
The main changes are as follows:
* Added a connString.xml file with the following format:
Note the two tags, one <conStringSQLE>, for SQL Server 2005 express, and <conStringSQL>, for SQL Server 2000 (& MSDE). Also, note the "Initial Catalog" term without anything after it. This is a ROOT conn string. The app uses this root to get both the Master DB as well as any selected DBs.
These are the defaults. If you want to connect to the DB in the CRUD app under a different user name, change it to something like this: data source=.\SQLEXPRESS;User ID=;Password=;Initial Catalog where you supply the User ID and Password (again, leave "Initial Catalog" by itself.)
*Added ability to select which DB to connect to at the beginning.
I am thinking about have the radio buttons autogenerated from the XML file. So far, these are the only 2 DBs I use, so I don't need it. I've thought about stripping out all the actual DB comm code to a separate file (a connProvider) that is specified in a modified connString.xml file. That way, if a person wanted to, they could write their own provider, add it to the xml file (name for radio button, location of class). But I'm not sure if I want to invest the time. The project is included for any changes people want to make.
As always, let me know if there are any problems.
Posts: 1346 | Registered: Jun 1999
| IP: Logged |
posted
I'm about hip deep in a few projects of my own right now, so I've got no time to look at this, but I wanted you to know I thought this was really cool.
Posts: 10177 | Registered: Apr 2001
| IP: Logged |
Printer-friendly view of this topic
![[Smile]](smile.gif)
