GridView 主从表编辑与新增范例代码

发布时间:2020-09-06编辑:脚本学堂
asp.net中gridview主从表编辑与新增的例子,主要有两个页面:1、PastList.aspx 主表列表页面 2、PastView.aspx 主从表编辑页面

asp.net中gridview主从表编辑与新增的例子,主要有两个页面:
1、PastList.aspx  主表列表页面
2、PastView.aspx 主从表编辑页面
SqlHelper.cs   数据访问类
一个实体类
一个枚举类

1、主表列表页面 PastList.aspx
 

复制代码 代码示例:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartList.aspx.cs" Inherits="PartList" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>主从表新增和编辑的测试</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="myGridView" runat=server AutoGenerateColumns=False HorizontalAlign=Center CellPadding="4" ForeColor="#333333" GridLines="None" Height="97px" Width="587px">
        <Columns>
            <asp:BoundField DataField="BigTypeName" HeaderText = "类别名称" />
            <asp:BoundField DataField="BigTypeDescribe" HeaderText = "类别描述" />
            <asp:TemplateField HeaderText="编辑">
                <ItemTemplate>
                    <a href='PartView.aspx?ID=<%# Eval("ID") %>'>编辑</a>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <EditRowStyle BackColor="#999999" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
        <center><asp:Button ID="Button1" runat="server" Text="新增" OnClick="Button1_Click" /></center>
        </div>
    </form>
</body>
</html>
 

1)、后台代码
 

复制代码 代码示例:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class PartList : System.Web.UI.Page
{
    SqlHelper sqlHelper = new SqlHelper();
    private static DataTable dtType;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            bindData(true);
    }

    /// <summary>
    /// 数据绑定
    /// </summary>
    /// <param name="refresh"></param>
    private void bindData(bool refresh)
    {

        if (refresh || dtType == null)
        {
            DataSet ds = sqlHelper.getDs("Select * from BigType order by TypeOrder", CommandType.Text, null, "BigType");
            dtType = ds.Tables[0];
        }

        this.myGridView.DataSource = dtType.DefaultView;
        this.myGridView.DataBind();
    }

    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Redirect("PartView.aspx");
    }
}

2、主从表编辑页面 PartView.aspx
 

复制代码 代码示例:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartView.aspx.cs" Inherits="PartView" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>主从表新增和编辑的测试</title>
    <script language="javascript">
        function checkFrm()
        {
            if(document.form1.txtBigTypeName.value.length==0)
            {
                alert("请输入类别名称");
                document.form1.txtBigTypeName.focus();
                return(false);
            }
            if(isNaN(document.form1.txtTypeOrder.value) || document.form1.txtTypeOrder.value.length==0)
            {
                alert("请输入数字");
                document.form1.txtTypeOrder.focus();
                return(false);
            }
        }
        function checkDel(str)
        {
            if(!confirm("确认删除 "+str+" 吗?"))
                return(false);
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        <table border="1" align="center" style="border-collapse:collapse">
               <tr>
                    <td align="right">类别名称:</td>
                    <td align="left"><asp:TextBox ID="txtBigTypeName" runat="server" MaxLength="50" Width="313px"></asp:TextBox></td>
               </tr>
               <tr>
                    <td align="right">类别描述:</td>
                    <td align="left"><asp:TextBox TextMode="MultiLine" Rows="7" Columns="50" ID="txtBigTypeDescribe" runat="server" MaxLength="500"></asp:TextBox></td>
               </tr>
               <tr>
                    <td align="right">类别排序:</td>
                    <td align="left"><asp:TextBox ID="txtTypeOrder" runat="server" MaxLength="50" Width="307px"></asp:TextBox></td>
               </tr>
               <tr>
                    <td align="center" colspan="2">
                        <asp:Button ID="BtSave" Text="保存" runat="server" OnClick="BtSave_Click" />&nbsp;&nbsp;&nbsp;
                        <asp:Button ID="BtAdd" Text="新增从记录" runat="server" OnClick="BtAdd_Click" />
                    </td>
               </tr>
        </table>      
        <asp:GridView ID="detailGridView" AutoGenerateColumns="False" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Height="79px" Width="527px" OnRowCancelingEdit="detailGridView_RowCancelingEdit" OnRowDeleting="detailGridView_RowDeleting" OnRowEditing="detailGridView_RowEditing" OnRowUpdating="detailGridView_RowUpdating" OnDataBound="detailGridView_DataBound">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText = "编号" />
                <asp:TemplateField HeaderText="小类名称">
                    <ItemTemplate>
                        <asp:Label ID="showSmallTypeName" runat="server" Text='<%# Eval("SmallTypeName") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSmallTypeName" runat="server" Text='<%# Eval("SmallTypeName") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="描述">
                    <ItemTemplate>
                        <asp:Label ID="showSmallTypeDescribe" runat="server" Text='<%# Eval("SmallTypeDescribe") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSmallTypeDescribe" runat="server" Text='<%# Eval("SmallTypeDescribe") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="排序">
                    <ItemTemplate>
                        <asp:Label ID="showSmallTypeOrder" runat="server" Text='<%# Eval("SmallTypeOrder") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSmallTypeOrder" runat="server" Text='<%# Eval("SmallTypeOrder") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:CommandField ShowEditButton="True" />
                <asp:CommandField ShowDeleteButton="True" />
            </Columns>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <EditRowStyle BackColor="#999999" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>
        <asp:TextBox ID="hidID" runat=server Text="" Visible=false></asp:TextBox>
    </div>
    </form>
</body>
</html>
 

2)、后台代码
 

复制代码 代码示例:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class PartView : System.Web.UI.Page
{
    private int BigTypeID;
    private string Sql;
    SqlHelper sqlHelper = new SqlHelper();
    private static DataTable dtSmallType = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        this.BtSave.Attributes.Add("onclick", "return checkFrm();");
        if (!Page.IsPostBack)
        {
            BigTypeID = Convert.ToInt32(Request.QueryString["id"]);
            this.hidID.Text = BigTypeID.ToString();

            if (BigTypeID != 0)
            {//表示修改状态
                bindBigType();
                bindData(true);
            }
            else
            {//新增时,把架构给dtSmallType
                fillTable();
            }
        }
        else
        {
            BigTypeID = Convert.ToInt32(this.hidID.Text);
        }
    }

    /// <summary>
    /// 取从表的架构给 dtSmallType
    /// </summary>
    private void fillTable()
    {
        Sql = "Select * from SmallType where 1=0";
        dtSmallType = sqlHelper.getDs(Sql, CommandType.Text, null, "SmallType").Tables[0];
    }

    /// <summary>
    /// 绑定主表
    /// </summary>
    private void bindBigType()
    {
        Sql = "Select * from BigType where ID=" + BigTypeID;
        SqlDataReader dr;
        dr = sqlHelper.getDr(Sql, CommandType.Text, null);

        while (dr.Read())
        {
            this.txtBigTypeName.Text = dr["BigTypeName"].ToString();
            this.txtBigTypeDescribe.Text = dr["BigTypeDescribe"].ToString();
            this.txtTypeOrder.Text = dr["TypeOrder"].ToString();
        }
    }

    /// <summary>
    /// 数据绑定,并且判断从表记录是否从数据库中从新读取
    /// </summary>
    /// <param name="refresh"></param>
    private void bindData(bool refresh)
    {
        //绑定从表数据
        if (refresh || dtSmallType == null)
        {
            Sql = "Select * from SmallType where BigTypeID=" + BigTypeID;
            dtSmallType = sqlHelper.getDs(Sql, CommandType.Text, null, "SmallType").Tables[0];
        }

        this.detailGridView.DataSource = dtSmallType.DefaultView;
        this.detailGridView.DataBind();
    }

    /// <summary>
    /// 新增从表记录
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtAdd_Click(object sender, EventArgs e)
    {
        DataRow row = dtSmallType.NewRow();
        row["ID"] = (int)getMaxIdInTable(dtSmallType, "ID") + 1;

        if (this.BigTypeID != 0)
        { //表示修改主从记录
            row["BigTypeID"] = BigTypeID;
        }
        dtSmallType.Rows.Add(row);

        this.detailGridView.EditIndex = dtSmallType.Rows.Count - 1;
        this.bindData(false);
    }

    /// <summary>
    /// 保存主表修改
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtSave_Click(object sender, EventArgs e)
    {
        if (this.detailGridView.EditIndex > -1)
        {
            Response.Write("<script language=javascript>alert('请先结束从表编辑');</script>");
            return;
        }

        BigTypeEntity entity = new BigTypeEntity();
        entity.BigTypeName     = this.txtBigTypeName.Text.ToString();
        entity.BigTypeDescribe = this.txtBigTypeDescribe.Text.ToString();
        entity.TypeOrder       = Convert.ToInt16(this.txtTypeOrder.Text);

        if (BigTypeID != 0)
        { //表示修改
            entity.ID = BigTypeID;
            sqlHelper.SaveBigTypeAndSmallType(entity, dtSmallType, EditMethod.Update);
        }
        else
        {//表示新增
            sqlHelper.SaveBigTypeAndSmallType(entity, dtSmallType, EditMethod.Insert);
            BigTypeID = (int)sqlHelper.getScalar("Select Top 1 ID from BigType Order By ID Desc", CommandType.Text, null);
            this.hidID.Text = BigTypeID.ToString();
        }

        this.bindData(true);
    }

    /// <summary>
    /// GridView编辑事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void detailGridView_RowEditing(object sender, GridViewEditEventArgs e)
    {
        this.detailGridView.EditIndex = e.NewEditIndex;
        this.bindData(false);
    }

    /// <summary>
    /// GridView撤消事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void detailGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        this.detailGridView.EditIndex = -1;
        this.bindData(false);
    }

    /// <summary>
    /// GridView更新事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void detailGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //保存在本地
        int i = this.detailGridView.EditIndex;
      
        string typeName = ((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeName")).Text.ToString();
        string typeDescribe = ((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeDescribe")).Text.ToString();
        int typeOrder = Convert.ToInt16(((TextBox)this.detailGridView.Rows[i].FindControl("txtSmallTypeOrder")).Text);
        dtSmallType.Rows[i]["SmallTypeName"] = typeName;
        dtSmallType.Rows[i]["SmallTypeDescribe"] = typeDescribe;
        dtSmallType.Rows[i]["SmallTypeOrder"] = typeOrder;

        this.detailGridView.EditIndex = -1;
        this.bindData(false);
    }

    /// <summary>
    /// GridView删除事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void detailGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int selectIndex = e.RowIndex;

        //这里的 Cells[0] 对应的是编号列
        int id = Convert.ToInt16(this.detailGridView.Rows[selectIndex].Cells[0].Text);

        //从数据库中删除
        string Sql;
        Sql = "Delete from SmallType where ID=" + id;
        sqlHelper.ExecuteSql(Sql, CommandType.Text, null);

        //从内存中删除
        DataRow[] dr = dtSmallType.Select("ID=" + id);
        if(dr.Length > 0)
            dtSmallType.Rows.Remove(dr[0]);

        this.bindData(false);
    }

    /// <summary>
    /// 得到指定表中关键字的最大值,此方法可以放在公共运行函数当中
    /// </summary>
    /// <param name="table"></param>
    /// <param name="keyID"></param>
    /// <returns></returns>
    private object getMaxIdInTable(DataTable table, string keyID)
    {
        if (table.Rows.Count == 0)
            return 0;
        DataView dv = new DataView();
        dv.Table = table;
        dv.Sort = keyID + " Desc";
        return dv[0][keyID];
    }


    protected void detailGridView_DataBound(object sender, EventArgs e)
    {
      
    }
}

3、数据库访问类 SqlHelper.cs
 

复制代码 代码示例:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlHelper
    {
        private const string ConnStr = "Data Source=127.0.0.1;Initial Catalog=HJ;User id=sa;Password=as;";
        private SqlConnection Conn = null;
        private SqlCommand Cmd = null;
        private SqlDataAdapter Adp = null;
        private DataSet ds = new DataSet();

        /// <summary>
        /// 返回 DataSet
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <param name="tableName"></param>
        public DataSet getDs(string cmdText, CommandType cmdType, SqlParameter[] pars, string tableName)
        {
            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            Adp = new SqlDataAdapter(Cmd);

            try
            {
                Conn.Open();
                if (tableName == null || tableName == string.Empty)
                    Adp.Fill(ds);
                else
                    Adp.Fill(ds, tableName);
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }


        /// <summary>
        /// 执行sql语句,返回受影响的行数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSql(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            int res = 0;
            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                res = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return res;
        }

        /// <summary>
        /// 返回 DataReader 对象
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public SqlDataReader getDr(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            SqlDataReader dr = null;

            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
            }
            return dr;
        }

        public object getScalar(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            object res = null;

            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                res = Cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return res;
        }

        /// <summary>
        /// 只为主从表保存测试使用
        /// </summary>
        /// <param name="entity">主表实体</param>
        /// <param name="dtSmallType">从表的Table,包含状态</param>
        /// <param name="method">方法,是否新增或者修改</param>
        public void SaveBigTypeAndSmallType(BigTypeEntity entity, DataTable dtSmallType, EditMethod method)
        {
            int BigTypeID;
            string Sql;
            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.CommandType = CommandType.Text;
            Cmd.Connection = Conn;

            Conn.Open();
            SqlTransaction tran = Conn.BeginTransaction();
            Cmd.Transaction = tran;

            try
            {
              
                if (method == EditMethod.Insert)
                {
                    //插入主表
                    Sql = "Insert Into BigType(BigTypeName,BigTypeDescribe,TypeOrder) values('" + entity.BigTypeName + "','" + entity.BigTypeDescribe + "'," + entity.TypeOrder + ")";
                    Cmd.CommandText = Sql;
                    Cmd.ExecuteNonQuery();

                    //取得刚才插入的 ID,如果主键是GUID,在这里处理就方便些
                    Cmd.CommandText = "Select Top 1 ID from BigType order By ID Desc";
                    BigTypeID = (int)Cmd.ExecuteScalar();

                    //插入从表
                    foreach (DataRow dr in dtSmallType.Rows)
                    {//新增状态下,从表所有行的DataRow属性都为Added
                        Sql = "Insert Into SmallType(SmallTypeName,SmallTypeDescribe,SmallTypeOrder,BigTypeID) ";
                        Sql += "values('" + dr["SmallTypeName"].ToString() + "','" + dr["SmallTypeDescribe"].ToString() + "'," + dr["SmallTypeOrder"].ToString() + "," + BigTypeID + ")";
                        Cmd.CommandText = Sql;
                        Cmd.ExecuteNonQuery();
                    }
                }
                else if (method == EditMethod.Update)
                {
                    //修改主表
                    Sql = "Update BigType Set BigTypeName='" + entity.BigTypeName + "',BigTypeDescribe='" + entity.BigTypeDescribe + "',TypeOrder=" + entity.TypeOrder + " where ID=" + entity.ID.ToString();
                    Cmd.CommandText = Sql;
                    Cmd.ExecuteNonQuery();

                    //插入从表
                    foreach (DataRow dr in dtSmallType.Rows)
                    {
                        if (dr.RowState == DataRowState.Added)
                        {
                            Sql = "Insert Into SmallType(SmallTypeName,SmallTypeDescribe,SmallTypeOrder,BigTypeID) ";
                            Sql += "values('" + dr["SmallTypeName"].ToString() + "','" + dr["SmallTypeDescribe"].ToString() + "'," + dr["SmallTypeOrder"].ToString() + "," + entity.ID.ToString() + ")";
                            Cmd.CommandText = Sql;
                            Cmd.ExecuteNonQuery();
                        }
                        else if (dr.RowState == DataRowState.Modified)
                        {
                            Sql = "Update SmallType Set SmallTypeName='" + dr["SmallTypeName"].ToString() + "',SmallTypeDescribe='" + dr["SmallTypeDescribe"].ToString() + "',";
                            Sql += "SmallTypeOrder=" + dr["SmallTypeOrder"].ToString() + " where ID=" + dr["ID"].ToString();
                            Cmd.CommandText = Sql;
                            Cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw new Exception(e.ToString());
            }
            finally
            {
                tran.Commit();
                Conn.Close();
            }
        }
    }

4、实体类
 

复制代码 代码示例:

public class BigTypeEntity
{
    private int _ID;

    private string _BigTypeName;

    private string _BigTypeDescribe;

    private int _TypeOrder;

    public int ID
    {
        get { return this._ID; }
        set { this._ID = value;}
    }

    public string BigTypeName
    {
        get { return this._BigTypeName; }
        set { this._BigTypeName = value; }
    }

    public string BigTypeDescribe
    {
        get { return this._BigTypeDescribe; }
        set { this._BigTypeDescribe = value; }
    }
    public int TypeOrder
    {
        get { return this._TypeOrder; }
        set { this._TypeOrder = value; }
    }
}

5、枚举
 

复制代码 代码示例:
public enum EditMethod
{
    Insert,
    Update
}

6、使用的数据库表的脚本
 

复制代码 代码示例:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BigType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BigType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SmallType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SmallType]
GO

CREATE TABLE [dbo].[BigType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BigTypeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BigTypeDescribe] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[TypeOrder] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SmallType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SmallTypeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SmallTypeOrder] [int] NULL ,
[SmallTypeDescribe] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BigTypeID] [int] NULL
) ON [PRIMARY]
GO