dbconn.cs

一款自动编码工具

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

using System.Collections;



namespace AutoCreateCode
{
	/// <summary>
	/// autoCodeClass 的摘要说明。
	/// </summary>
	public class DBConn
	{
		private static SqlConnection conn;
		private static string filePath = "";
//		public DBConn()
//		{
//			//
//			// TODO: 在此处添加构造函数逻辑
//			//
//		}

		public DBConn(string _filePath)
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
			filePath = _filePath;
			string sqlconn;
			try
			{
				INIClass objiniclass	= new INIClass(filePath);	
				sqlconn = objiniclass.IniReadValue("strconn","sqlconnstring");
			
				if( sqlconn != "")
				{
					conn = new SqlConnection(sqlconn);
				}
			}
			catch
			{
			}

		}
		public  DataTable GetTableInfo(string tableName)
		{
			if( conn == null )
			{
				return null;
			}

			string sqlconn;
			try
			{
				INIClass objiniclass	= new INIClass(filePath);	
				sqlconn = objiniclass.IniReadValue("strconn","sqlconnstring");
			
				if( sqlconn != "")
				{		
					conn.ConnectionString	= sqlconn;
				}
			}
			catch
			{
				return null;
			}
		
	
			SqlCommand cmd	   = new SqlCommand();
			cmd.Connection	   = conn;

			cmd.CommandText	   = " SELECT"+   
								 " TABLE_NAME=case   when   a.colorder=1   then   d.name   else   d.name   end,"+
								 " TABLE_EXPLAIN=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,"+
								 " COLUM_ID=a.colorder,"+
								 " column_name=a.name,"+
								 " IDENTIFIER=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   'v'else   ''   end,"+
								 " FK=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   ("+
								 " SELECT   name   FROM   sysindexes   WHERE   indid   in("+
								 " SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid"+
								 " )))   then   'v'   else   ''   end,"+
								 " type_name=b.name,"+
								 " length=a.length,"+
								 " PLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'),"+
								 " num=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),"+
								 " IsNu=case   when   a.isnullable=1   then   'v'else   ''   end,"+
								 " Defualt=isnull(e.text,''),"+
								 " ColumDescription=isnull(g.[value],'')"+
								 " FROM   syscolumns   a"+
								 " left   join   systypes   b   on   a.xtype=b.xusertype"+
								 " inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'"+
								 " left   join   syscomments   e   on   a.cdefault=e.id"+
								 " left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid"+     
								 " left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0"+
								 " where   d.name='"+ tableName +"'"+        
								 " order   by   a.id,a.colorder";

			SqlDataAdapter	da = new SqlDataAdapter(cmd);
            
			DataTable dt	   = new DataTable();
			try
			{
				da.Fill(dt);
			}
			catch( System.Exception e )
			{
				throw e;
			}

			return dt;
		}

		/// <summary>
		/// 得到数据库服务器上所有的数据库名称
		/// </summary>
		/// <returns></returns>
		public DataTable GetDataBaseName()
		{
			if( conn == null )
			{
				return null;
			}

			SqlCommand	cmd		= new SqlCommand();
			cmd.Connection		= conn;
			cmd.CommandType		= CommandType.StoredProcedure;
			cmd.CommandText		= "sp_helpdb";

			SqlDataAdapter da	= new SqlDataAdapter( cmd );
			DataTable dt		= new DataTable();
			try
			{
				da.Fill( dt );

			}
			catch
			{
				return null;
			}

			return dt;


		}

		/// <summary>
		/// 得到库中的所有用户表
		/// </summary>
		/// <param name="DBTableName"></param>
		/// <returns></returns>
		public  DataTable GetTableName(string DBTableName)
		{
			if( conn == null )
			{
				return null;
			}

			string sqlconn;
			try
			{
				INIClass objiniclass	= new INIClass(filePath);	
				sqlconn = objiniclass.IniReadValue("strconn","sqlconnstring");
			
				if( sqlconn != "")
				{
					string[] temp			=  sqlconn.Split(';');
					temp[1]					= "database="+DBTableName;
					conn.ConnectionString	= temp[0]+";"+temp[1]+";"+temp[2]+";"+temp[3]+";";
				}
			}
			catch
			{
				return null;
			}
	
			SqlCommand cmd			= new SqlCommand();
			cmd.Connection	   = conn;
			//U代表用户表
			cmd.CommandText	   = " select   *   from   sysobjects   where   xtype='U' order by [Name]";

			SqlDataAdapter	da = new SqlDataAdapter(cmd);
            
			DataTable dt	   = new DataTable();
			try
			{
				da.Fill(dt);
			}
			catch
			{
				return null;
			}

			return dt;
		}


		/// <summary>
		/// 测试数据库连接
		/// </summary>
		/// <param name="filePath"></param>
		/// <returns></returns>
		public static bool TestConn(string strconn)
		{

			SqlConnection conn; 
			try
			{				
				if( strconn != null)
				{
					conn = new SqlConnection(strconn);
					conn.Open();
					conn.Close();
					return true;
				}
				else
				{
					return false;
				}
				
			}
			catch
			{
				return false;
			}
			
		}
	}
}