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

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

amende

my english very poor.

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

the templet files is not enough in reason .

hope

hope that net firend will incessant upgrade

create code examples

____________________________________________________ .CS

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

... ...