Auto create data layout c# code and sql procedure for .net 2005

• Download source files - 357 Kb
• Download demo project - 355 Kb

amende

instruction

This is a .net2005 Addins ,Use to auto create c# source code and

sql procedure by database table logic and templet files for operated database.

theory

first: get the database information by sql system table or sql sp_help

command, the information include table name,table column,table column type......

 public DataSet GetTableInfo(string tableName)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter("dbo.sp_help [" + 

tableName + "]", connSql);

            try
            {
                connSql.Open();
                adp.Fill(ds);
                DataSet dsTmp = new DataSet();
                dsTmp = GetTableInfo(dsTmp.Tables[0].Rows[0]

["Name"].ToString(), "name");
                ds.Tables[0].Columns.Add("Id");
                ds.Tables[0].Rows[0]["Id"] = dsTmp.Tables[0].Rows[0]["Id"];

            }
            catch (Exception e)
            {
                string i = e.Message;
            }
            finally
            {
                connSql.Close();
            }
            return ds;
        }

second: create table logic by database information , and save information to a xml file.

thirdly: create templet files

___________________________________________sql procedure templet files

<add% @-outputfield-="@@IDENTITY" />
<add%
CREATE PROCEDURE dbo.p_<%tabname%>_Add
 <%paddparm%>
AS
BEGIN 
 INSERT INTO [<%tabname%>]
 <%addintfield%>
 VALUES
 <%addvaluefield%>
 
 <iden- SET @-outputfield- = @@IDENTITY -iden>
END
<%GO%>
%add>
... ...

___________________________________________source code templet files

    public class <%class%>OP
    {
        private SqlConnection conn=new 

SqlConnection(@"<%connstr%>");

        public <%class%>OP()
        {
        }

        public ArrayList <%tabname%>_Get(<parkey- />out string E)
        {
            SqlDataReader dr<%tabname%>=null;
            SqlCommand cmd<%tabname%>=new SqlCommand();
            

cmd<%tabname%>.CommandType=CommandType.StoredProcedure;
            cmd<%tabname%>.Connection=conn;
            cmd<%tabname%>.CommandText="p_<%tabname%>_Get";
            
            
<KEYPAR-

            


cmd#tabname#.Parameters.Add("@#field#",#fieldtype#,#fieldlength#);

            
cmd#tabname#.Parameters["@#field#"].Value=#field#;

            
-keypar> 
            
            ArrayList al<%tabname%>=new ArrayList();
            
            try
            {
                E="1";
                conn.Open();            
                

dr<%tabname%>=cmd<%tabname%>.ExecuteReader();
                while(dr<%tabname%>.Read())
                {
                    <%tabname%> src<%class%>=new 

<%class%>();
                    
                

    <GETSRC# #getsrc src#tabname#.#field#='#convertstr#(dr#tabname#["#field#"]);'>
                    
        

            al<%tabname%>.Add(src<%tabname%>);
                }
            }
            catch(Exception e)
            {
                E=e.Message;
                al<%tabname%>=null;
            }
            dr<%tabname%>.Close();
            conn.Close();
            return al<%tabname%>;
        }
... ...

fourthly: create source code file by database information and table

logic xml files, create sql procedure by database information.

(use database information replace templet files some code,

for example:<%tabname%> is sql table name)

defect

hope

create code examples

using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;

namespace DodeAutoTest
{
 public class OrderDetailsOP
 {
  private SqlConnection conn=new 

SqlConnection(@"server=info-jinlei\netsdk;database=northwind;user 

id=sa;pwd=sa");

  public OrderDetailsOP()
  {
  }

  public ArrayList OrderDetails_Get(out string E)
  {
   SqlDataReader drOrderDetails=null;
   SqlCommand cmdOrderDetails=new SqlCommand();
   cmdOrderDetails.CommandType=CommandType.StoredProcedure;
   cmdOrderDetails.Connection=conn;
   cmdOrderDetails.CommandText="p_OrderDetails_Get";
   
   
   
   ArrayList alOrderDetails=new ArrayList();
   
   try
   {
    E="1";
    conn.Open();   
    drOrderDetails=cmdOrderDetails.ExecuteReader();
    while(drOrderDetails.Read())
    {
     OrderDetails srcOrderDetails=new OrderDetails();
     
     
     srcOrderDetails.OrderID=Convert.ToInt32(drOrderDetails["OrderID"]);

... ...

_____________________________________________________________ sql procedure

ALTER  PROCEDURE dbo.p_OrderDetails_UpdByOrderID
 @OrderID int,
 @ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real
AS
BEGIN
 UPDATE
 [OrderDetails]
 SET
 

ProductID=@ProductID,UnitPrice=@UnitPrice,Quantity=@Quantity,Discount=@Disco

unt
 WHERE
 OrderID = @OrderID
END

GO

... ...

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here