SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
来源:互联网 发布:spring cloud golang 编辑:程序博客网 时间:2024/04/29 09:18
- Download source file - 44.4 Kb
- Download demo file - 21.9 Kb
What is this?
DBHelper is a small tool that will generate either a source file ora compiled assembly for selected stored procedures on your SQL server.It uses the CodeDom
namespace to output thecode. Currently it supports C# and VB.NET code generation (and otherlanguage support is likely in the future), but the generated assemblyis usable from any .NET language. Also included in version 1.5 is theability to compile a complete assembly that includes a typed DataSet
based on your SQL stored procedures output.
Basically, this is an all-in-one tool for wrapping a SQL database.Great for existing databases, and for rapidly changing databases. E.g.changes gets made to stored procedure's parameters or the output ofstored procedures. No problem for DBHelper, just a few clicks and you ahave all the methods remapped/DataSet
regenerated. All you have to do it adjust your corresponding .NET methods.
NOTE: DBNull
s are still not supported. I'mstill looking for a way. The alternative is to still generate thesource file and the XSD file, and change all the method parameters fromtheir strong type to System.Object
. That's all! Hopefully there wont be too many!
New information (version 1.5+)
As of version 1.4, the package comes as a tool rather than alibrary. Please select the SQL provider (no others will work and I haveNO plans to support it). If you tick Create, it will create acorresponding method, and CreateStrong
will generate an XSD for strongly typed DataSet
/Tables.
Also note VS.NET is NO longer required for typed DataSet
generation and compilation (overlooked a static method).
Version 1.6 includes plenty code optimization. Also, typed datasetsnow get created based on return values. The is no more option to selectwhich ones you want. I have tested it successfully on the MASTERdatabase.
Why?
Some people might ask "Why?". The answer is sweet. Take the following for example:
Every time you even want to execute a simple stored procedure you need something like the following:
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "ChangeEmail";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id_param;
cmd.Parameters.Add("@oldemail", SqlDbType.VarChar).Value = oldemail_param;
cmd.Parameters.Add("@newemail", SqlDbType.VarChar).Value = newemail_param;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
Now that's only 3 parameters, how about some nasty UPDATE
procedures with 10+ parameters? That can be a real time waster,especially doing all the type mapping by hand. On the other hand youcould always go for DataSet
s, and let VS.NET do the work. My personal liking is to use DataSet
s only for SELECT
queries and I usually do all the other statements via SqlCommand
'sas above. The library will generate all the required parameters andpresent you with a strongly typed wrapper method for the storedprocedure.
UPDATE: I have now updated the library to return a DataTable
of the output. For example:
public static DataTable ChangeEmail(SqlConnection connection
DataTable table, long id_param,
string oldemail_param, string newemail_param)
The code from the former example is automatically inserted into aclass of choice. Now that looks a lot better, and will probably saveyou countless hours.
How does it work?
These methods are a bit redundant as of version 1.4 as it run as a tool rather than a library.Firstly your database will be queried for stored procedures. After thatwe get the name and type of the parameters for each procedure. With thehelp of CodeDom
we create a new class to output to a source file or an assembly.
Constructor
SP(SqlConnection conn, CodeDomProvider cp);
Just a reference to a predefined
SqlConnection
. See example forCodeDomProvider
usage.E.g.
SP sp = new SP(myConnection, new CSharpCodeProvider);
Properties
public SPCollection Names;
Returns a strongly typed string collection of all the storedprocedure names. This allows you to remove the ones you don't needmethods for (as in the UI example).
Methods
public CompilerResults Compile(string ns, string classname, string asmname);
Compiles to an assembly (
asmname
) using values from Names property to generate. Returns results (primarily for debugging).public CompilerResults Compile(string[] storedprocs, string ns, string classname, string asmname);
Compiles to an assembly (
asmname
) with own defined string array of stored procedures to generate. Returns results (primarily for debugging).public void CreateFile(string[] storedprocs, string ns, string classname, string filename);
Same as compiler, but will output source file.
public void CreateFile(string ns, string classname, string filename);
Same as compiler, but will output source file.
Notes
I use a very, very simple mapping from SqlDbType
to Type
and DBNull
values are not supported either in this release.
For the test app to work you will need to change the SqlConnection
to your linking.
Free plug
Kristopher Johnson - Typed Collection Class Generator for creatingall the collections and dictionaries in DBHelper (although the CodeDomcode was not looked at).
Conclusion
This was really my first successful use of CodeDom and it seemsreally powerful, especially if you use it correctly as output to anylanguage should be possible. As usual, bugs, problems, praise,suggestions, gifts can be added below.
UPDATE: I have created support for VB.NET code as well.
Cheers :)
License
This article, along with any associated source code and files, is licensed under The BSD License
About the Author
leppie
Member
Occupation: Software DeveloperLocation: South Africa
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- SPGen - Stored Procedure Generator
- SPGen - Stored Procedure Generator
- Stored procedure & Sql Injection
- Quartz integration within Spring for Invoke SQL stored procedure
- Get Dataset from Stored Procedure in Oracle
- Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
- Sql server stored procedure operating summary 01
- Sql server database stored procedure reference 01
- SQL Server 2000 Stored Procedure Programming
- Assignment 1: PL/SQL stored procedure P9
- Overview of SQL Server Stored Procedure
- SQL存储过程(Stored Procedure)
- Stored Procedure
- C# Code Generator for Stored Procedures
- C# Code Generator for Stored Procedures
- Dejan Sunderic, «Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET» (3rd edition)
- 得走了
- priority_queue & 结构体
- WebBrowser.Navigate异步运行,如何使其与其他进程同步?
- 大家好
- 今天下载
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- SPGen - Stored Procedure Generator
- Qos RTCP
- SQL Server存储过程
- java jsp tomcat6 mysql 连接池配置
- VC调用存储过程的通用方法(SQLServer篇)
- 对于近期博客状况的说明
- E-R图
- Tomcat 6.0配置数据源(DBCP)和JNDI使用