通过C#程序生成数据库的实体类，根据SqlServer存储过程生成数据操作类

---页面部分winForm程序

 

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

 

namespace WindowsApplication1
{
 /// <summary>
 /// Form1 的摘要说明。
 /// </summary>
 public class Form1 : System.Windows.Forms.Form
 {
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.ListBox lst_Tables;
  private System.Windows.Forms.Button btn_Create;
  private System.Windows.Forms.Button button2;
  private System.Windows.Forms.GroupBox groupBox1;
  private System.Windows.Forms.RadioButton rb_table;
  private System.Windows.Forms.RadioButton rb_Procedure;
  private System.Windows.Forms.Button btn_GetObject;
  private System.Windows.Forms.Button button3;
  /// <summary>
  /// 必需的设计器变量。
  /// </summary>
  private System.ComponentModel.Container components = null;

  public Form1()
  {
   //
   // Windows 窗体设计器支持所必需的
   //
   InitializeComponent();

   
  }

  /// <summary>
  /// 清理所有正在使用的资源。
  /// </summary>
  protected override void Dispose( bool disposing )
  {
   if( disposing )
   {
    if (components != null)
    {
     components.Dispose();
    }
   }
   base.Dispose( disposing );
  }

  #region Windows 窗体设计器生成的代码
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.button1 = new System.Windows.Forms.Button();
   this.lst_Tables = new System.Windows.Forms.ListBox();
   this.btn_Create = new System.Windows.Forms.Button();
   this.button2 = new System.Windows.Forms.Button();
   this.groupBox1 = new System.Windows.Forms.GroupBox();
   this.rb_table = new System.Windows.Forms.RadioButton();
   this.rb_Procedure = new System.Windows.Forms.RadioButton();
   this.btn_GetObject = new System.Windows.Forms.Button();
   this.button3 = new System.Windows.Forms.Button();
   this.groupBox1.SuspendLayout();
   this.SuspendLayout();
   //
   // button1
   //
   this.button1.Location = new System.Drawing.Point(16, 200);
   this.button1.Name = "button1";
   this.button1.Size = new System.Drawing.Size(75, 56);
   this.button1.TabIndex = 0;
   this.button1.Text = "生成实体类";
   this.button1.Click += new System.EventHandler(this.button1_Click);
   //
   // lst_Tables
   //
   this.lst_Tables.HorizontalScrollbar = true;
   this.lst_Tables.ItemHeight = 12;
   this.lst_Tables.Location = new System.Drawing.Point(0, 0);
   this.lst_Tables.Name = "lst_Tables";
   this.lst_Tables.SelectionMode = System.Windows.Forms.SelectionMode.MultiSimple;
   this.lst_Tables.Size = new System.Drawing.Size(288, 184);
   this.lst_Tables.TabIndex = 1;
   //
   // btn_Create
   //
   this.btn_Create.Location = new System.Drawing.Point(104, 200);
   this.btn_Create.Name = "btn_Create";
   this.btn_Create.Size = new System.Drawing.Size(80, 56);
   this.btn_Create.TabIndex = 2;
   this.btn_Create.Text = "CreateTable类型实体类";
   this.btn_Create.Click += new System.EventHandler(this.btn_Create_Click);
   //
   // button2
   //
   this.button2.Location = new System.Drawing.Point(192, 200);
   this.button2.Name = "button2";
   this.button2.Size = new System.Drawing.Size(75, 56);
   this.button2.TabIndex = 3;
   this.button2.Text = "生成存储过程";
   this.button2.Click += new System.EventHandler(this.button2_Click);
   //
   // groupBox1
   //
   this.groupBox1.Controls.Add(this.rb_Procedure);
   this.groupBox1.Controls.Add(this.rb_table);
   this.groupBox1.Location = new System.Drawing.Point(320, 8);
   this.groupBox1.Name = "groupBox1";
   this.groupBox1.Size = new System.Drawing.Size(128, 100);
   this.groupBox1.TabIndex = 4;
   this.groupBox1.TabStop = false;
   this.groupBox1.Text = "groupBox1";
   //
   // rb_table
   //
   this.rb_table.Location = new System.Drawing.Point(16, 24);
   this.rb_table.Name = "rb_table";
   this.rb_table.TabIndex = 0;
   this.rb_table.Text = "Table";
   //
   // rb_Procedure
   //
   this.rb_Procedure.Location = new System.Drawing.Point(16, 64);
   this.rb_Procedure.Name = "rb_Procedure";
   this.rb_Procedure.TabIndex = 1;
   this.rb_Procedure.Text = "Procedure";
   //
   // btn_GetObject
   //
   this.btn_GetObject.Location = new System.Drawing.Point(336, 136);
   this.btn_GetObject.Name = "btn_GetObject";
   this.btn_GetObject.TabIndex = 5;
   this.btn_GetObject.Text = "GetObject";
   this.btn_GetObject.Click += new System.EventHandler(this.btn_GetObject_Click);
   //
   // button3
   //
   this.button3.Location = new System.Drawing.Point(288, 200);
   this.button3.Name = "button3";
   this.button3.Size = new System.Drawing.Size(75, 56);
   this.button3.TabIndex = 6;
   this.button3.Text = "根据存储过程生成数据类";
   this.button3.Click += new System.EventHandler(this.button3_Click);
   //
   // Form1
   //
   this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
   this.ClientSize = new System.Drawing.Size(600, 317);
   this.Controls.Add(this.button3);
   this.Controls.Add(this.btn_GetObject);
   this.Controls.Add(this.groupBox1);
   this.Controls.Add(this.button2);
   this.Controls.Add(this.btn_Create);
   this.Controls.Add(this.lst_Tables);
   this.Controls.Add(this.button1);
   this.Name = "Form1";
   this.Text = "Form1";
   this.groupBox1.ResumeLayout(false);
   this.ResumeLayout(false);

  }
  #endregion

  /// <summary>
  /// 应用程序的主入口点。
  /// </summary>
  [STAThread]
  static void Main()
  {
   Application.Run(new Form1());
  }

  private void button1_Click(object sender, System.EventArgs e)
  {

//   DataSystemType _DataSystemType = new DataSystemType();
//
//   System.Data.DataSet _DS;
//   _DS = _DataSystemType.SystemTypeQuery();
//
//   QuickCoder.table2CSharp(_DS,"tempNameSpace","tempclass");

  System.Windows.Forms.ListBox.SelectedObjectCollection _SelObj  =this.lst_Tables.SelectedItems;

   int n=_SelObj.Count;
   for(int i=0;i<n;i++)
   {
    string tableName = ((System.Windows.Forms.ListBox.SelectedObjectCollection)_SelObj)[i].ToString();
    MakeFile( tableName );
   }

  
  }

  private void MakeFile(string TableName)
  {

   //根据表名生成dataSet

   System.Data.DataSet _DS ;

   DataAccess _DataAccess = new DataAccess();
   _DS = _DataAccess.GetDataSet(TableName);

   //生成文件
   QuickCoder.table2CSharp(_DS,"tempNameSpace", TableName);

   
  }

  private void MakeDataProc(string  ProcName)
  {
   System.Data.DataSet _DS ;

   DataAccess _DataAccess = new DataAccess();
   _DS = _DataAccess.GetDataSetOfProc(ProcName);

   //生成文件
   QuickCoder.procedure2CSharp(_DS,"tempNameSpace", ProcName);
  }

  private void FillList()
  {
   DataAccess _DataAccess = new DataAccess();

   
   System.Data.DataSet _DS = new DataSet() ;

   if (this.rb_table.Checked)
   {
    _DS = _DataAccess.GetTableList();
   }
   else if (this.rb_Procedure.Checked)
   {
    _DS = _DataAccess.GetProcedureList();
   }

   this.lst_Tables.Items.Clear();

   foreach (System.Data.DataRow dr in _DS.Tables[0].Rows)
   {
    this.lst_Tables.Items.Add(dr[0].ToString());
   }

  }

  private void btn_Create_Click(object sender, System.EventArgs e)
  {
   

   System.Windows.Forms.ListBox.SelectedObjectCollection _SelObj  =this.lst_Tables.SelectedItems;

   int n=_SelObj.Count;
   for(int i=0;i<n;i++)
   {
    string tableName = ((System.Windows.Forms.ListBox.SelectedObjectCollection)_SelObj)[i].ToString();
    Create( tableName );
   }

   }
  

  private void Create(string TableName)
  {
  
   DataAccess _DataAccess = new DataAccess();
  
   MessageBox.Show(  _DataAccess.ExportDataLayer(TableName,"tempNameSpace", TableName) );
  }

  private void ExportStoreProcedure(string TableName)
  {
  
   DataAccess _DataAccess = new DataAccess();
  
   MessageBox.Show(  _DataAccess.ExportStoreProcedure(TableName) );
  }

  private void button2_Click(object sender, System.EventArgs e)
  {
   //ExportStoreProcedure

    System.Windows.Forms.ListBox.SelectedObjectCollection _SelObj  =this.lst_Tables.SelectedItems;

   int n=_SelObj.Count;
   for(int i=0;i<n;i++)
   {
    string tableName = ((System.Windows.Forms.ListBox.SelectedObjectCollection)_SelObj)[i].ToString();
    ExportStoreProcedure( tableName );
   }

  }

  private void btn_GetObject_Click(object sender, System.EventArgs e)
  {
   

   FillList();
 }

  private void button3_Click(object sender, System.EventArgs e)
  {
  
   System.Windows.Forms.ListBox.SelectedObjectCollection _SelObj  =this.lst_Tables.SelectedItems;

   int n=_SelObj.Count;
   for(int i=0;i<n;i++)
   {
    string procName = ((System.Windows.Forms.ListBox.SelectedObjectCollection)_SelObj)[i].ToString();
    MakeDataProc( procName );
   }

  }

  

 }
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

代码文件部分

 

using System;
using System.Xml;
using System.Data;

using System.IO;

namespace WindowsApplication1
{
 
 
 public class QuickCoder
 {
  public static string MSSQLServerDataTypeCSharpTypeMap (System.Type _type)
  {

   string _Result = string.Empty;

   switch( _type.ToString() )
   {
    case "char":
     _Result = "string";
     break;
    case "varchar" :
     _Result = "string";
     break;
    case "nvarchar" :
     _Result = "string";
     break;

    case "text":
     _Result = "string";
     break;
    case "datetime":
     _Result =  "System.DateTime";
     break;
    case "int":
     _Result =  "int";
     break;

    case "int identity":
     _Result =  "float";
     break;
    case "float":
     _Result =  "float";
     break;
    case "bit":
     _Result =  "string";
     break;
    case "numeric":
     _Result =  "double";
     break;
    case "money":
     _Result =  "double";
     break;
 
 

   }

  
   return _Result;
  }
  
  public static string MSSQLServerDataTypeCSharpTypeMap (string _type)
  {

   string _Result = string.Empty;

   switch( _type )
   {
    case "char":
     _Result = "string";
     break;
    case "varchar" :
     _Result = "string";
     break;
    case "nvarchar" :
     _Result = "string";
     break;

    case "text":
     _Result = "string";
     break;
    case "datetime":
     _Result =  "System.DateTime";
     break;
    case "int":
     _Result =  "int";
     break;

    case "int identity":
     _Result =  "float";
     break;
    case "float":
     _Result =  "float";
     break;
    case "bit":
     _Result =  "string";
     break;
    case "numeric":
     _Result =  "double";
     break;
    case "money":
     _Result =  "double";
     break;
 
 

   }

  
   return _Result;
  }

 

  public static string MSSQLServerDataTypeMap (string _type)
  {

   string _Result = string.Empty;

   switch( _type.ToLower() )
   {

 
 
 
 
 

    
     case "nchar":
     _Result =  "NChar";
     break;
          
     case "ntext":
     _Result =  "NText";
     break;
     case "nvarchar":
     _Result =  "NVarChar";
     break;
 
 
 
 
 
    
     case "real":
     _Result =  "Real";
     break;
          
     case "smalldatetime":
     _Result =  "SmallDateTime";
     break;
         
     case "smallint":
     _Result =  "SmallInt";
     break;
     
     case "smallmoney":
     _Result =  "SmallMoney";
     break;
          
     case "text":
     _Result =  "Text";
     break;
     case "timestamp":
     _Result =  "Timestamp";
     break;
  
 
 
 
          
     case "uniqueidentifier":
     _Result =  "UniqueIdentifier";
     break;
           
     case "varbinary":
     _Result =  "VarBinary";
     break;
           
     case "varchar":
     _Result =  "VarChar";
     break;
           
     case "variant":
     _Result =  "Variant";
     break;

     case "tinyint":
     _Result =  "TinyInt";
     break;
        
     case "money":
     _Result =  "Money";
     break;
     
     case "int":
     _Result =  "Int";
     break;
     
     case "image":
     _Result =  "Image";
     break;
 

     case "float":
     _Result =  "Float ";
     break;
     

     case "decimal":
     _Result =  "Decimal";
     break;
     
     case "datetime":
     _Result =  "DateTime";
     break;
     
     case "char":
     _Result =  "Char";
     break;
     
     case "bit":
     _Result =  "Bit";
     break;
     
     case "binary":
     _Result =  "Binary";
     break;
     
     case "bigint":
     _Result =  "BigInt";
     break;
 
 
 

 

 

   }

  
   return _Result;
  }
  public static void table2CSharp(
   System.Data.DataSet  rsmd,
   String _namespace, String className) 
  {
   System.Text.StringBuilder cs = new  System.Text.StringBuilder (),
    csFiled = new System.Text.StringBuilder(),
    csProperty = new System.Text.StringBuilder();
   string line = "/r/n";
 
   className = className+"Model";

   //加入命名空间

   cs.Append("using System;").Append(line).Append(line);
   cs.Append("/*").Append(line);
   cs.Append(" *本代码部分为程序自动生成").Append(line);
   cs.Append(" * 使用前请检查").Append(line);
   cs.Append(" */").Append(line).Append(line);
   cs.Append("namespace ").Append(_namespace).Append(" {");
   cs.Append(line).Append(line);

   //加入类名
   cs.Append("/// <summary>").Append(line);
   cs.Append("//实体类").Append(line);
   cs.Append("/// </summary>").Append(line);
   cs.Append(" public class ").Append(className).Append(" {");
   cs.Append(line).Append(line);

   int c = rsmd.Tables[0].Columns.Count;
   System.Data.DataColumn[] pKeycolum = rsmd.Tables[0].PrimaryKey;

   string  type, name,pKey =string.Empty;
   System.Object t;

   for (int i = 0; i <= c-1; i++)
   {
    name = rsmd.Tables[0].Columns[i].ColumnName.Replace(' ', '_');
    System.Type dbType = rsmd.Tables[0].Columns[i].DataType ;
    string allowDBNull ="字段允许为空："+ rsmd.Tables[0].Columns[i].AllowDBNull.ToString();
    string maxLong  ="字段长度："+ rsmd.Tables[0].Columns[i].MaxLength.ToString();
    
    foreach( System.Data.DataColumn colum in pKeycolum)
    {
     if (name == colum.ColumnName)
     {
      pKey = "主键";
     }
    }
   
    t = dbType.ToString();

    string summary = string.Empty;

    summary = "///"+ name + line
     +"///"+  allowDBNull + line
     +"///"+ maxLong  + line;

    type = MSSQLServerDataTypeCSharpTypeMap(dbType);

    if (t != null)
    {
     type = t.ToString();
    }
    else
    {
     type = "___" + dbType;
     //prt(namespace + "" + className + " " + dbType);
    }

    //加入私有字段
    csFiled.Append(line).Append(line);
    csFiled.Append("/// <summary>").Append(line);
    csFiled.Append("///私有字段").Append(line);
    csFiled.Append(summary);
    csFiled.Append("/// </summary>").Append(line);
    csFiled.Append(" private ").Append(type).Append(" _").Append(name).Append(";").Append(line);
    

    //加入共有方法
    csProperty.Append(line).Append(line);
    csProperty.Append("/// <summary>").Append(line);
    csProperty.Append("///公有属性").Append(line);
    csProperty.Append(summary);
    csProperty.Append("/// </summary>").Append(line);
    csProperty.Append(" public ").Append(type).Append(" ").Append(name).Append(line);
    csProperty.Append("{").Append(line);
    csProperty.Append(" get {").Append(line);
    csProperty.Append(" return ").Append(" _").Append(name).Append(";").Append(line);
    csProperty.Append("}").Append(line);
    csProperty.Append(" set {").Append(line);
    csProperty.Append("  _").Append(name).Append(" = value ;").Append(line);
    csProperty.Append("}").Append(line);
    csProperty.Append("}").Append(line);
     

 

   
   }
   cs.Append(csFiled).Append(csProperty);
   cs.Append(" }").Append(line);
   cs.Append("}").Append(line);

   System.IO.File.Delete( className + ".cs");
   System.IO.StreamWriter sw = System.IO.File.CreateText( className + ".cs");

   sw.Write(cs.ToString());

   sw.Close();

   
  
  }

  public static void procedure2CSharp(
   System.Data.DataSet  rsmd,
   String _namespace, String className) 
  {
   System.Text.StringBuilder cs = new  System.Text.StringBuilder (),
    csParms = new System.Text.StringBuilder(),
    csAddParam = new System.Text.StringBuilder(),
    csParamValue = new   System.Text.StringBuilder();

 

   string line = "/r/n";
   className = className.Replace("sp_","").Replace("SP_","");

   
   //加入命名空间

   cs.Append("using System;").Append(line).Append(line);
   cs.Append("/*").Append(line);
   cs.Append(" *本数据访问层代码部分为程序自动生成").Append(line);
   cs.Append(" * 使用前请检查").Append(line);
   cs.Append(" */").Append(line).Append(line);
   cs.Append("namespace ").Append(_namespace).Append(" {");
   cs.Append(line).Append(line);

   //加入类名
   cs.Append("/// <summary>").Append(line);
   cs.Append("//数据访问类").Append(line);
   cs.Append("/// </summary>").Append(line);
   cs.Append(" public class ").Append(className+ "Data").Append(" {");
   cs.Append(line).Append(line);

  // cs.Append("public static bool className("

   int c = rsmd.Tables[0].Rows.Count;
   
  // string  type, name,pKey =string.Empty;
   System.Object t;
   
   //循环没一行
   for (int i = 0; i <= c-1; i++)
   {
    
    string paramName = rsmd.Tables[0].Rows[i][0].ToString();
    string dataType = rsmd.Tables[0].Rows[i][1].ToString();
    string dataLeng = rsmd.Tables[0].Rows[i][2].ToString();
    string cSharpType = MSSQLServerDataTypeCSharpTypeMap( dataType );
    string cSharpName =  paramName.Replace('@',' ');
    dataType =MSSQLServerDataTypeMap(dataType);

    if (csParms.Length>0)
    {
     csParms.Append( ",");
    }
    csParms.Append( cSharpType + " "+cSharpName);

   
    if ( csAddParam.Length==0)
    {
     csAddParam.Append("System.Data.SqlClient.SqlParameter[] SqlParameter;");
    
     csAddParam.Append(line);

     csAddParam.Append(" SqlParameter = new System.Data.SqlClient.SqlParameter[]{");

     csAddParam.Append(line);

   
    }
   
    csAddParam.Append(" new System.Data.SqlClient.SqlParameter(/"" +paramName+"/",System.Data.SqlDbType."+ dataType+","+ dataLeng +"),").Append(line);
   
    csAddParam.Append(line);

    csParamValue.Append("SqlParameter[" + i.ToString() + "].Value="+cSharpName + ";").Append(line);

  
   }
 
   cs.Append("public static bool "+className+"(" +csParms.ToString()+ ")").Append(line);

   cs.Append("{").Append(line);
   cs.Append(" bool IsResult = false; ").Append(line);
   cs.Append("  try ").Append(line);
   cs.Append("{").Append(line);
   cs.Append(" using(System.Data.SqlClient.SqlConnection conn=new System.Data.SqlClient.SqlConnection (SQLConfig.GetSQLConnStr())){").Append(line);
   cs.Append(" conn.Open ();").Append(line);

   cs.Append(csAddParam).Append(line);
   cs.Append("};").Append(line);
   cs.Append(csParamValue).Append(line);

   cs.Append( "IsResult = SQLHelper.ExecuteNonQueryProc(conn,/""+ className +"/",SqlParameter"+ ");").Append(line);
   cs.Append(" }").Append(line);
   cs.Append(" }").Append(line);
   cs.Append(" catch(System.Data.SqlClient.SqlException ex){");
   cs.Append(" throw (new Exception(ex.Message));").Append(line);
   cs.Append("}").Append(line);
   cs.Append("return IsResult;").Append(line);
   
   cs.Append("}").Append(line);
   cs.Append("}").Append(line);
   cs.Append("}").Append(line);

 

  
WriteToFile(cs.ToString(),className + "Data.cs");

   
   
//   
//   cs.Append(" }").Append(line);
//   cs.Append("}").Append(line);
//
//   System.IO.File.Delete( className + ".cs");
//   System.IO.StreamWriter sw = System.IO.File.CreateText( );
//
//   sw.Write(cs.ToString());
//
//
//   sw.Close();

   
  
  }

 

  public static void WriteToFile(string str,string fileName)
  {
   if(File.Exists(fileName))
    File.Delete(fileName);
   FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
   StreamWriter w = new StreamWriter(fs);
   w.WriteLine(str);
   w.Flush();
   w.Close();
   fs.Close();
  }

 }

 

}

 

public class DataAccess
{

 public string GetConnString ()
 {
  string strPath = @"XMLDataSet.xml";
  string _SqlConn = string.Empty;
   
  XmlDocument xmlDoc = new XmlDocument();
  xmlDoc.Load( strPath );

  XmlNodeList nodeList=xmlDoc.SelectSingleNode("DATA").ChildNodes;

  foreach(XmlNode xn in nodeList)//遍历所有子节点
  {
   XmlElement xe=(XmlElement)xn;//将子节点类型转换为XmlElement类型
   //TODO:

   _SqlConn = xe.InnerText;
    
  }

  return _SqlConn;
  
 }

 public System.Data.DataSet GetTableList()
 {
   
  string _SqlConn = string.Empty;

  
  _SqlConn =  GetConnString();
   
  System.Data.SqlClient.SqlConnection myConnection;

  myConnection = new System.Data.SqlClient.SqlConnection(_SqlConn);

  string myQuery = "select name from dbo.sysobjects where type ='U'";

  //System.Data.SqlClient.SqlCommand myCommand =new  System.Data.SqlClient.SqlCommand(myQuery,myConnection);

  myConnection.Open();
  System.Data.DataSet myDataSet = new System.Data.DataSet();
   

  try
  {
   System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(myQuery,myConnection);
   
   myAdapter.Fill(myDataSet);
  }
  finally
  {
   // always call Close when done reading.
   //myReader.Close();
   // always call Close when done reading.
   myConnection.Close();
  }

  return myDataSet;

 }

 public System.Data.DataSet GetProcedureList()
 {
   
  string _SqlConn = string.Empty;

  
  _SqlConn =  GetConnString();
   
  System.Data.SqlClient.SqlConnection myConnection;

  myConnection = new System.Data.SqlClient.SqlConnection(_SqlConn);

  string myQuery = "select [name] from dbo.sysobjects where type ='P' ORDER BY  [name]";

  //System.Data.SqlClient.SqlCommand myCommand =new  System.Data.SqlClient.SqlCommand(myQuery,myConnection);

  myConnection.Open();
  System.Data.DataSet myDataSet = new System.Data.DataSet();
   

  try
  {
   System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(myQuery,myConnection);
   
   myAdapter.Fill(myDataSet);
  }
  finally
  {
   // always call Close when done reading.
   //myReader.Close();
   // always call Close when done reading.
   myConnection.Close();
  }

  return myDataSet;

 }

 public System.Data.DataSet GetDataSet(string strTableName )
 {

  string _SqlConn = string.Empty;

  _SqlConn =  GetConnString();

  System.Data.SqlClient.SqlConnection myConnection;

  myConnection = new System.Data.SqlClient.SqlConnection(_SqlConn);

  string myQuery = "select * from "+strTableName+" where 1>2 ";

  myConnection.Open();
  System.Data.DataSet myDataSet = new System.Data.DataSet();
   

  try
  {
   System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(myQuery,myConnection);
   myAdapter.Fill(myDataSet);
  }
  finally
  {
   myConnection.Close();
  }

  return myDataSet;

 

 }

 public System.Data.DataSet GetDataSetOfProc(string strProc)
 {
  string _SqlConn = string.Empty;

  _SqlConn =  GetConnString();

  System.Data.SqlClient.SqlConnection myConnection;

  myConnection = new System.Data.SqlClient.SqlConnection(_SqlConn);

  string myQuery = "select a.name '参数',b.name '类型',a.length '长度' "+
   " from syscolumns a,systypes b "+
   " where id = object_id('" +strProc +"')" +
   " and a.xtype=b.xtype "+
   " order by colorder ";

  myConnection.Open();
  System.Data.DataSet myDataSet = new System.Data.DataSet();
   

  try
  {
   System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(myQuery,myConnection);
   myAdapter.Fill(myDataSet);
  }
  finally
  {
   myConnection.Close();
  }

  return myDataSet;

 }
 /////////////////////////////////////////////////////////////////////////////////////////////////////////

 /// <summary>
 /// /////////////////////
 /// </summary>
 /// <param name="tableName"></param>
 /// <returns></returns>

 public string ExportDataLayer(string tableName,String _namespace, String className)
 {
  //Popo.Dal.SqlHelp.SqlConnString = "Data Source=localhost;Initial Catalog="+DrpDB.SelectedValue+";User ID=sa;Password=123";
  //   string sql = "select * from ["+tableName+"]";
  //   = Popo.Dal.SqlHelp.LoadData(sql).Tables[0];
  //
  DataAccess _DataAccess = new DataAccess();

  DataTable dt = _DataAccess.GetDataSet(tableName).Tables[0];

  string tbName = tableName;
  string outStr ="";
  className = tbName+"Data";
  outStr += "//FileName:"+className+".cs/n";
  outStr += "namespace "+_namespace+"{/n"+
   "/tusing System;/n"+
   "/tusing System.Data;/n/n"+
   "/t/// <summary>/n"+
   "/t/// "+className+" 的摘要说明。/n"+
   "/t/// </summary>/n"+
   "/t[System.ComponentModel.DesignerCategory(/"Code/")]/n"+
   "/t[SerializableAttribute]/n"+
   "/tpublic class "+className+" : DataSet{/n"+
   "/t/tpublic "+className+"(){/n"+
   //build table
   "/t/t/tDataTable table = new DataTable(/""+tbName+"/");/n"+
   "/t/t/tDataColumnCollection dcc = table.Columns;/n"+
   "/t/t/t/n";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += "/t/t/tdcc.Add(/""+dc.ColumnName+"/",typeof("+dc.DataType+"));/n";
  }
  outStr += "/t/t/tTables.Add(table);/n";
  outStr += "/t/t}/n/n";
 
  //property
  outStr += "/t/t#region 属性/n";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += "/t/tpublic "+dc.DataType+" "+dc.ColumnName+"{/n";
   outStr += "/t/t/tget{/n";
   outStr += "/t/t/t/treturn ("+dc.DataType+")Tables[/""+tbName+"/"].Rows[0][/""+dc.ColumnName+"/"];/n";
   outStr += "/t/t/t}/n";
   outStr += "/t/t}/n/n";
  }
  outStr += "/t/t#endregion/n";
 
  outStr += "/t}/n";//End Class
  outStr += "}"; //End NameSpace
 
  
 
  //System.Web.HttpContext context = System.Web.HttpContext.Current;
 
  //string file = context.Server.MapPath(context.Request.ApplicationPath) + @"/../Common/Data/"+className+".cs";
  WriteToFile(outStr,className+".cs");

  return outStr;
 }
 
 public void WriteToFile(string str,string fileName)
 {
  if(File.Exists(fileName))
   File.Delete(fileName);
  FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
  StreamWriter w = new StreamWriter(fs);
  w.WriteLine(str);
  w.Flush();
  w.Close();
  fs.Close();
 }
 
 public string ExportStoreProcedure(string tableName)
 {
  //   string sql = "select * from ["+tableName+"]";
  //   DataTable dt = Popo.Dal.SqlHelp.LoadData(sql).Tables[0];

  DataAccess _DataAccess = new DataAccess();
  DataTable dt = _DataAccess.GetDataSet(tableName).Tables[0];
  string outStr = "";
 
  //Add Data
  outStr += "If Exists (Select Name From dbo.SysObjects Where Name = 'Sp"+tableName+"Add')/n";
  outStr += "/tDrop Procedure Sp"+tableName+"Add/n";
  outStr += "Go/n";
  outStr += "CREATE PROCEDURE [dbo].[Sp"+tableName+"Add]/n";
  outStr += "(/n";
  //params
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += "/t@"+dc.ColumnName+" "+ConvertToSqlType(dc.DataType).ToString()+",/n";
  }
  outStr = outStr.Remove(outStr.Length-2,2);
  outStr += "/n)/n";
  outStr += "AS/n";
  outStr += "/tInsert Into ["+tableName+"] (/n/t/t";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += dc.ColumnName+",";
  }
  outStr = outStr.Remove(outStr.Length-1,1);
  outStr += "/n/t) Values (/n/t/t";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += "@"+dc.ColumnName+",";
  }
  outStr = outStr.Remove(outStr.Length-1,1);
  outStr += "/n/t)/n";
  outStr += "Go";
  //   foreach(DataColumn dc in dt.Columns){
  //    //dc.ColumnName
  //   }
  //Select Data
  outStr += "/n/nIf Exists (Select Name From dbo.SysObjects Where Name = 'Sp"+tableName+"GetAll')/n";
  outStr += "/tDrop Procedure Sp"+tableName+"GetAll/n";
  outStr += "Go/n";
  outStr += "CREATE PROCEDURE [dbo].[Sp"+tableName+"GetAll]/n";
  outStr += "AS/n";
  outStr += "/tSelect ";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += dc.ColumnName+",";
  }
  outStr = outStr.Remove(outStr.Length-1,1);
  outStr += " From "+tableName;
  outStr += "/nGo";
   
 
  //Select By Id
  outStr += "/n/nIf Exists (Select Name From dbo.SysObjects Where Name = 'Sp"+tableName+"GetBy"+dt.Columns[0].ColumnName+"')/n";
  outStr += "/tDrop Procedure Sp"+tableName+"GetBy"+dt.Columns[0].ColumnName+"/n";
  outStr += "Go/n";
  outStr += "CREATE PROCEDURE [dbo].[Sp"+tableName+"GetBy"+dt.Columns[0].ColumnName+"]/n";
  outStr += "(/n";
  outStr += "/t @"+dt.Columns[0].ColumnName+" "+ConvertToSqlType(dt.Columns[0].DataType).ToString()+"/n";
  outStr += ")/n";
  outStr += "AS/n";
  outStr += "/tSelect ";
  foreach(DataColumn dc in dt.Columns)
  {
   outStr += dc.ColumnName+",";
  }
  outStr = outStr.Remove(outStr.Length-1,1);
  outStr += " From "+tableName;
  outStr += "/n/t/t Where "+dt.Columns[0].ColumnName+"=@"+dt.Columns[0].ColumnName;
  outStr += "/nGo";
 
  //Delete By Id
  outStr += "/n/nIf Exists (Select Name From dbo.SysObjects Where Name = 'Sp"+tableName+"DelBy"+dt.Columns[0].ColumnName+"')/n";
  outStr += "/tDrop Procedure Sp"+tableName+"DelBy"+dt.Columns[0].ColumnName+"/n";
  outStr += "Go/n";
  outStr += "CREATE PROCEDURE [dbo].[Sp"+tableName+"DelBy"+dt.Columns[0].ColumnName+"]/n";
  outStr += "(/n";
  outStr += "/t @"+dt.Columns[0].ColumnName+" "+ConvertToSqlType(dt.Columns[0].DataType).ToString()+"/n";
  outStr += ")/n";
  outStr += "AS/n";
  outStr += "/tDelete From "+tableName+" Where "+dt.Columns[0].ColumnName+"=@"+dt.Columns[0].ColumnName;
  outStr += "/nGo";
 
  WriteToFile(outStr,"test.sql");

  return outStr;
   
 }
 
 public SqlDbType ConvertToSqlType(System.Type type)
 {
 
  SqlDbType sqlType = SqlDbType.VarChar;
  switch(type.ToString())
  {
   case "System.String":
    sqlType = SqlDbType.VarChar;
    break;
   case "System.Int32":
    sqlType = SqlDbType.Int;
    break;
   case "System.Boolean":
    sqlType = SqlDbType.Bit;
    break;
   case "System.DateTime":
    sqlType = SqlDbType.DateTime;
    break;
   case "System.Guid":
    sqlType = SqlDbType.UniqueIdentifier;
    break;
   case "System.Double":
    sqlType = SqlDbType.Float;
    break;
   case "System.Byte[]":
    sqlType = SqlDbType.Binary;
    break;
   case "System.Decimal":
    sqlType = SqlDbType.Money;
    break;
   default:
    break;
  }
  return sqlType;
 }
 

 }