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

namespace ServiceManager
{
	/// <summary>
	/// Class1 的摘要说明。
	/// </summary>
	public class CreateDBStruct
	{	
		private System.Data.SqlClient.SqlConnection DBConnection = null;
		
		/// <summary>
		/// 构造函数（无参数）
		/// </summary>
		public CreateDBStruct()
		{
			
		}
		/// <summary>
		/// 构造函数（重载）
		/// </summary>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns></returns>
		public CreateDBStruct(System.Data.SqlClient.SqlConnection sqlConnection)
		{	
			try
			{
				AddConnection(sqlConnection);
			}
			catch(Exception ex)
			{
				throw ex;
			}

		}
		
		/// <summary>
		/// 为类的数据库连接变量赋值
		/// </summary>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns></returns>
		public void AddConnection(System.Data.SqlClient.SqlConnection sqlConnection)
		{
			try
			{
				if(sqlConnection == null)
				{
					throw new Exception("不能赋空实例");
				}
				else
				{
					DBConnection = sqlConnection;
					DBConnection.Open();
					DBConnection.ChangeDatabase("master");
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		/// <summary>
		/// 建立数据库
		/// </summary>
		/// <param name="DBName">数据库名</param>
		/// <returns></returns>
		public void CreateDataBase(string DBName)
		{
			try
			{
				CreateDataBase(DBName,this.DBConnection);
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// 建立数据库（重载）
		/// </summary>
		/// <param name="DBName">数据库名</param>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns></returns>
		public void CreateDataBase(string DBName,System.Data.SqlClient.SqlConnection sqlConnection)
		{	
			try
			{	
				this.DBConnection = sqlConnection;

				if(this.DBConnection!=null)
				{
					this.ExecuteSql("","CREATE"+" DATABASE\""+DBName+"\"",DBConnection);
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
		
		
		
		}
		
		/// <summary>
		/// 利用文本建立数据表，建表的sql语句从文本导入
		/// </summary>
		/// <param name="DBName">数据库名</param>
		/// <param name="filename">文本文件名</param>
		/// <returns></returns>
		public void CreateDataTables(string DBName,string filename,string divider)
		{
			try
			{
				CreateDataTables(DBName,filename,divider,this.DBConnection);
		
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
	
		/// <summary>
		/// 利用文本建立数据表，建表的sql语句从文本导入（重载）
		/// </summary>
		/// <param name="DBName">数据库名</param>
		/// <param name="filename">文本文件名</param>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns></returns>
		public void CreateDataTables(string DBName,string filename,string divider,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			string Whole = "",subSql="";
			this.AddConnection(sqlConnection);
			try
			{
				Whole = this.GetSql(filename);
				
				int index=0;
                if(!String.IsNullOrEmpty(divider))
				{
					while((index=Whole.IndexOf(divider,index))>=0)
					{
						if( ( Char.IsWhiteSpace(Whole[index-1]) )&&( Char.IsWhiteSpace(Whole[index+divider.Length]) ) )
						{
							subSql = Whole.Substring(0,index);
							this.ExecuteSql(DBName,subSql);
							Whole = Whole.Remove(0,index+divider.Length);
							index=0;
						}
						else
						{
							index++;
						}
					
					}
				}
				else
				{
					this.ExecuteSql(DBName,Whole);
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// 在指定的数据库中执行sql语句，是所有创建功能的基础
		/// </summary>
		/// <param name="DataBaseName">数据库名</param>
		/// <param name="Sql">要执行的脚本</param>
		/// <returns></returns>
		public int  ExecuteSql(string DataBaseName,string Sql)
		{
			int rownum=0;
			try
			{
				rownum = ExecuteSql(DataBaseName,Sql,this.DBConnection);
			}
			catch(Exception ex)
			{
				throw ex;
			}
			return rownum;
		}
		/// <summary>
		/// 在指定的数据库中执行sql语句（重载）
		/// </summary>
		/// <param name="DataBaseName">数据库名</param>
		/// <param name="Sql">要执行的脚本</param>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns></returns>
		public int ExecuteSql(string DataBaseName,string Sql,System.Data.SqlClient.SqlConnection sqlConnection)
		{	
			int rownum=0;
			this.DBConnection = sqlConnection;
			if(DBConnection.State == ConnectionState.Closed)
				DBConnection.Open();
			System.Data.SqlClient.SqlCommand Command = null;

			try
			{
				Command = new System.Data.SqlClient.SqlCommand(Sql,DBConnection);
				
					
				if(!String.IsNullOrEmpty(DataBaseName))
					Command.Connection.ChangeDatabase(DataBaseName);

				rownum = Command.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw ex;
			}
			finally
			{
				if(DBConnection.State == ConnectionState.Open)
					DBConnection.Close();
			}
			return rownum;
		}
		
		/// <summary>
		/// 读取指定文本中的sql脚本
		/// </summary>
		/// <param name="filename">文本文件名</param>
		/// <returns>读出的内容</returns>
		public string GetSql(string filename)
		{
			try
			{
				StreamReader reader = new StreamReader(filename,System.Text.Encoding.GetEncoding("GB2312"));
				string content = reader.ReadToEnd();
				reader.Close();
				return content;
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		
		/// <summary>
		/// 读取指定文本中的sql脚本来创建脚本
		/// </summary>
		/// <param name="DBName">数据库名</param>
		/// <param name="filename">文本文件名</param>
		/// <param name="divider">分割符</param>
		/// <param name="sqlConnection">数据库链接</param>
		/// <returns>读出的内容</returns>
		public void CreateProcedures(string DBName,string filename,string divider,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			string Whole = "",subSql="";
			this.AddConnection(sqlConnection);
			try
			{
				Whole = this.GetSql(filename);
				
				int index=0;
                if (!String.IsNullOrEmpty(divider))
				{
					while((index=Whole.IndexOf(divider,index))>=0)
					{
						if( ( Char.IsWhiteSpace(Whole[index-1]) )&&( Char.IsWhiteSpace(Whole[index+divider.Length]) ) )
						{
							subSql = Whole.Substring(0,index);
							this.ExecuteSql(DBName,subSql);
							Whole = Whole.Remove(0,index+divider.Length);
							index=0;
						}
						else
						{
							index++;
						}
					
					}
				}
				else
				{
					this.ExecuteSql(DBName,Whole);
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		public void CreateProcedures(string DBName,string filename,string divider)
		{
			try
			{
				CreateProcedures(DBName,filename,divider,this.DBConnection);
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}
		public bool DBExisted(string DBName)
		{
			return DBExisted(DBName,this.DBConnection);
		}
		public bool DBExisted(string DBName,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			bool result = false;
			
			try
			{
				result = this.SelectFrom("","select * from sysdatabases where name = '"+DBName+"'",sqlConnection);
			}
			catch(Exception ex)
			{
				result = false;
				throw ex;
			}
			finally
			{
				if(DBConnection.State == ConnectionState.Open)
					DBConnection.Close();
			}
			return result;
		}

		public bool LoginExisted(string LoginName)
		{
			return LoginExisted(LoginName,this.DBConnection);
		}
		public bool LoginExisted(string LoginName,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			bool result = false;
			
			try
			{
				result = this.SelectFrom("","select * from syslogins where loginname = '"+LoginName+"'",sqlConnection);
			}
			catch(Exception ex)
			{
				result = false;				
				throw ex;
			}
			finally
			{
				if(DBConnection.State == ConnectionState.Open)
					DBConnection.Close();
			}
			return result;
		}

		public bool SelectFrom(string DBName,string SqlString)
		{
			return this.SelectFrom(DBName,SqlString,this.DBConnection);
		}
		
		public bool SelectFrom(string DBName,string SqlString,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			bool result = false;
			string Sql ="";
			
			System.Data.SqlClient.SqlCommand Command = null;
			try
			{
				this.DBConnection = sqlConnection;
				if(DBConnection.State == ConnectionState.Closed)
					DBConnection.Open();
				if(!String.IsNullOrEmpty(DBName))
				{
					DBConnection.ChangeDatabase(DBName);
				}
				else
				{
					DBConnection.ChangeDatabase("master");
				}
				
				Sql = SqlString;
				Command = new System.Data.SqlClient.SqlCommand(Sql,DBConnection);
				
				object o=Command.ExecuteScalar();
				result = (o!=null);
			}
			catch(Exception ex)
			{
				result = false;
				throw ex;
			}
			finally
			{
				if(DBConnection.State == ConnectionState.Open)
					DBConnection.Close();
			}
			return result;
			
		}

		public bool DeleteLogin(string LoginName)
		{
			return DeleteLogin(LoginName,this.DBConnection);
		}
		public bool DeleteLogin(string LoginName,System.Data.SqlClient.SqlConnection sqlConnection)
		{
			bool result = false;
			string Sql ="";
			
			System.Data.SqlClient.SqlCommand Command = null;
			SqlDataAdapter sqlDA = null;
			try
			{
				this.DBConnection = sqlConnection;
				if(DBConnection.State == ConnectionState.Closed)
					DBConnection.Open();
				
				DataSet m_ds = new DataSet();
				Command = new System.Data.SqlClient.SqlCommand(Sql,DBConnection);
				DBConnection.ChangeDatabase("master");
				Sql="select name from sysdatabases";
				Command.CommandType = CommandType.Text;
				Command.CommandText = Sql;
				
				sqlDA = new SqlDataAdapter();
				sqlDA.SelectCommand = Command;
				sqlDA.Fill(m_ds);
				
				if(m_ds.Tables[0].Rows.Count==0)
				{
					result = true;
				}
				else
				{
					for(int i=0;i<m_ds.Tables[0].Rows.Count;i++)
					{
						if(this.SelectFrom(m_ds.Tables[0].Rows[i][0].ToString(),"select * from sysusers where name = '"+LoginName+"'"))
						{
							this.ExecuteSql(m_ds.Tables[0].Rows[i][0].ToString(),"exec sp_dropuser '"+LoginName+"'");
							result = true;
						}
					}
				}

				this.ExecuteSql("master","exec sp_droplogin '"+LoginName+"'");
			}
			catch(Exception ex)
			{
				result = false;
				throw ex;
			}
			finally
			{
				if(DBConnection.State == ConnectionState.Open)
					DBConnection.Close();
			}
			return result;
		}

	}
}