SQL Server Stored Procedure and C# Object Code Builder
http://blogs.elysianonline.com/blogs/derek/pages/SQL-Server-Stored-Procedure-and-C_2300_-Object-Code-Builder.aspx
Download the source for the application here: Elysian Productions Inc. Code Builder 1.0 (428 KB)
SQL 파라메터 쿼리를 작성할때 불편한점이 있다.
전에
[Mssql] sp 호출 c#코드를 만들어 주는 sp(Stored Procedure)
대한 글이 있었는 데 프로시져를 이용해서 쿼리작성을 편하게 하는 방법이였는 데 이것 보다더 좋은 프로젝트가 있다.
테이블에 10~20개의 컬럼이 있다면 이컬럼을 모두 파라메터쿼리로 작성하려고
작성하려면 프로젝트 진행할때 시간이 참 많이 걸린다.
전에 봤던 Sql Stored Procedure로 만든것이 있었는 데 이것보다 더 적합하고 편리한 프로젝트가 있어서
소개하려고 한다.
맨위에 외국 블로그 링크는 적어 두었다.
솔루션 파일은 Visual Studio 2008로 작성이 되어 있고,
insert, update, delete 등 쿼리 작성시 필요한 모든 쿼리를 만들 수 있다.
원하는 테이블을 선택하여 Generate 버튼을 누르면 된다.
작성해야 하는 insert 파라메터쿼리는
insertCmdHead.Parameters.Add("@gubun", SqlDbType.VarChar, 2, "gubun");
insertCmdHead.Parameters.Add("@itemcode", SqlDbType.VarChar, 20, "itemcode");
insertCmdHead.Parameters.Add("@branch", SqlDbType.VarChar, 20, "branch");
insertCmdHead.Parameters.Add("@barcode", SqlDbType.VarChar, 20, "barcode");
insertCmdHead.Parameters.Add("@itemtype", SqlDbType.Int, "itemtype");
insertCmdHead.Parameters.Add("@itemname", SqlDbType.VarChar, 100, "itemname");
insertCmdHead.Parameters.Add("@sname", SqlDbType.VarChar, 100, "sname");
insertCmdHead.Parameters.Add("@price", SqlDbType.Money, "price");
insertCmdHead.Parameters.Add("@page", SqlDbType.Money, "page");
insertCmdHead.Parameters.Add("@weight", SqlDbType.Money, "weight");
insertCmdHead.Parameters.Add("@unitcode", SqlDbType.VarChar, 3, "unitcode");
이렇게 20개정도 된다면 다 작성을 해야 한다.
그런데 이것을 자동적으로 생성을 해주는 것이다.
원래 결과는 이렇게 나오지 않지만 지금 진행중인 프로젝트에 맞게 소스를 수정하였다.
DB의 경우 MSSQL 2000, 2005를 모두 지원한다.
C#코드도 자동적으로 작성을 해주기 때문에 편리하게 작성을 할 수가 있을 거 같다.
문제)
처음 프로젝트 실행할때 테이블 구조가 맞지 않는 다고 에러가 났다.
Problem reading table. PK is not named PK_<TableName>
라면서 에러가 났다. 에러난 부분의 코드를 주석처리하고 예외처리를 해주면 된다.
public DBForeignKey PrimaryKey
{
get
{
DBForeignKey tmp = new DBForeignKey();
try
{
//tmp = _relationships["PK_" + _name];
tmp = _relationships[_name];
}
catch (Exception e)
{
//throw new ApplicationException("Problem reading table. PK is not named PK_<TableName>", e);
}
return tmp;
}
}
아마도 Table구조가 틀리다는 말 같다.
코드작성에 수고를 덜수 있어서 잘되었다. ㅎ
http://www.codeproject.com/KB/database/ 에서 더 많은 정보를 찾아 볼 수 도있다.
Over the past few years I've been using and developing a tool that I wrote to help me generate SQL Server stored procedures and the corresponding C# object code to access the stored procedures. I thought I would release the tool here under GPL so that other might find it useful in their projects. The goals I wanted to accomplish with this tool are as follows:
1) Remove the tedium of writing stored procedures.
2) Remove the tedium of writing C# data access layer code.
3) Make writing business logic easier.
4) Make any tool used in the process work for about 80% of the code I encounter.
The last point is the most significant one. It also happens to be why I can never get my hands around things like the Entity Framework or LINQ. These frameworks end up trying to solve every edge case so that they can maintain some pretty diagram inside the Visual Studio IDE. In the process, they generate code that is more complex, difficult to use and difficult to debug. Maybe I'm just an old programmer guy now, but I like simplicity, ease of use and clean code.
Before I set out to build the tool, I needed to know what problems I needed to solve and over the past two years the tool has adapted to what it is today to accommodate my needs. Feel free to do the same with the code and make it work for you. Remember, the goal is not to make the application program for you, but to make your programming easier because of the application. The tool has evolved to cover the following features:
* SqlTransaction support to string C# data access objects together under a single transaction.
* Auditing of before and after data. (Database SQL and common code objects are in the resources folder)
* Generation of disable/enable code for check boxes.
* Support for a common base class that helps eliminate redundant code. (Set this option in the application's setting file.)
* Support for generating code using foreign keys and partial composite primary key lookups.
* Support for SQL Server 2000/2005/2008.
The best I can tell you is to play with the code builder and see what the output is. All of the code is generated in MainForm.cs and is properly commented so that you can find the generating pieces and change them according to your needs. Couple of assumptions. The application requires .Net 2.0 or higher. Indexes need to start with IX_* in order to be recognized as secondary indexes. Primary keys need to start with PK_* in order to be recognized properly as primary keys. The latter is important because that's how the code determines parameters to pass to certain methods like delete and update. The last thing the application assumes is that every table has a DateCreated and a DateModified datetime column with a default getdate()/getutcdate(). Minor edits to the stored procedures will have to happen if these rules are not followed. Other than that, most anything you can build in SQL Server can be recognized by the application and of course, feel free to edit away the assumed coding practices that I use if they do not fit your situation.
Download the source for the application here: Elysian Productions Inc. Code Builder 1.0 (428 KB)
Unzip the contents to a folder under My Documents or along side your other Visual Studio 2008 projects.
