C# SQLHelper.Close方法代码示例

 /// <summary>
 /// Creates a database out of the structure it is given
 /// </summary>
 /// <param name="Database">Database structure</param>
 /// <param name="ConnectionString">The connection string to the database's location</param>
 public static void CreateDatabase(Database Database,string ConnectionString)
 {
     try
     {
         string Command = BuildCommands(Database);
         string[] Splitter = { "\n" };
         string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
         string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "");
         using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.ExecuteNonQuery();
             }
             catch { throw; }
             finally { Helper.Close(); }
         }
         for (int x = 1; x < Commands.Length; ++x)
         {
             using (SQLHelper Helper = new SQLHelper(Commands[x], ConnectionString, CommandType.Text))
             {
                 try
                 {
                     Helper.Open();
                     Helper.ExecuteNonQuery();
                 }
                 catch { throw; }
                 finally { Helper.Close(); }
             }
         }
     }
     catch { throw; }
 }

 /// <summary>
 /// Sets up the views
 /// </summary>
 /// <param name="ConnectionString">Connection string</param>
 /// <param name="Temp">Database object</param>
 private static void SetupViews(string ConnectionString, Database Temp)
 {
     foreach (View View in Temp.Views)
     {
         string Command = "SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE [email protected]";
         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.AddParameter("@ViewName",128, View.Name);
                 Helper.ExecuteReader();
                 if (Helper.Read())
                 {
                     View.Definition = (string)Helper.GetParameter("Definition", "");
                 }
             }
             catch { }
             finally { Helper.Close(); }
         }
         Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE] FROM sys.views INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id WHERE (sys.views.name = @ViewName) AND (sys.systypes.xusertype <> 256)";
         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.AddParameter("@ViewName",128, View.Name);
                 Helper.ExecuteReader();
                 while (Helper.Read())
                 {
                     string ColumnName = (string)Helper.GetParameter("Column", "");
                     string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
                     int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
                     if (ColumnType == "nvarchar")
                         MaxLength /= 2;
                     bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
                     View.AddColumn<string>(ColumnName, ColumnType.TryTo<string, SqlDbType>().ToDbType(), MaxLength, Nullable);
                 }
             }
             catch { }
             finally { Helper.Close(); }
         }
     }
 }

 /// <summary>
 /// Sets up the tables (pulls columns, etc.)
 /// </summary>
 /// <param name="ConnectionString">Connection string</param>
 /// <param name="Temp">Database object</param>
 private static void SetupTables(string ConnectionString, Database Temp)
 {
     foreach (Table Table in Temp.Tables)
     {
         string Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE], sys.columns.is_identity as [IS IDENTITY], sys.index_columns.index_id as [IS INDEX], key_constraints.name as [PRIMARY KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN KEY TABLE], columns_1.name as [FOREIGN KEY COLUMN], sys.default_constraints.definition as [DEFAULT VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @TableName) AND (sys.systypes.xusertype <> 256)";
         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.AddParameter("@TableName",128, Table.Name);
                 Helper.ExecuteReader();
                 while (Helper.Read())
                 {
                     string ColumnName = (string)Helper.GetParameter("Column", "");
                     string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
                     int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
                     if (ColumnType == "nvarchar")
                         MaxLength /= 2;
                     bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
                     bool Identity = (bool)Helper.GetParameter("IS IDENTITY", false);
                     bool Index = (bool)((int)Helper.GetParameter("IS INDEX", 0) != 0);
                     bool PrimaryKey = string.IsNullOrEmpty((string)Helper.GetParameter("PRIMARY KEY", "")) ? false : true;
                     bool Unique = string.IsNullOrEmpty((string)Helper.GetParameter("UNIQUE", "")) ? false : true;
                     string ForeignKeyTable = (string)Helper.GetParameter("FOREIGN KEY TABLE", "");
                     string ForeignKeyColumn = (string)Helper.GetParameter("FOREIGN KEY COLUMN", "");
                     string DefaultValue = (string)Helper.GetParameter("DEFAULT VALUE", "");
                     if (Table.ContainsColumn(ColumnName))
                     {
                         Table.AddForeignKey(ColumnName, ForeignKeyTable, ForeignKeyColumn);
                     }
                     else
                     {
                         Table.AddColumn(ColumnName, ColumnType.TryTo<string, SqlDbType>().ToDbType(), MaxLength, Nullable, Identity, Index, PrimaryKey, Unique, ForeignKeyTable, ForeignKeyColumn, DefaultValue);
                     }
                 }
             }
             catch { }
             finally { Helper.Close(); }
         }
         Command = "SELECT sys.triggers.name as Name,sys.trigger_events.type as Type,OBJECT_DEFINITION(sys.triggers.object_id) as Definition FROM sys.triggers INNER JOIN sys.trigger_events ON sys.triggers.object_id=sys.trigger_events.object_id INNER JOIN sys.tables on sys.triggers.parent_id=sys.tables.object_id where [email protected]";
         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.AddParameter("@TableName",128, Table.Name);
                 Helper.ExecuteReader();
                 while (Helper.Read())
                 {
                     string Name = (string)Helper.GetParameter("Name", "");
                     int Type = (int)Helper.GetParameter("Type", 0);
                     string Definition = (string)Helper.GetParameter("Definition", "");
                     Table.AddTrigger(Name, Definition, Type.ToString().TryTo<string, TriggerType>());
                 }
             }
             catch { }
             finally { Helper.Close(); }
         }
     }
     foreach (Table Table in Temp.Tables)
     {
         Table.SetupForeignKeys();
     }
 }

 /// <summary>
 /// Sets up stored procedures
 /// </summary>
 /// <param name="ConnectionString">Connection string</param>
 /// <param name="Temp">Database object</param>
 private static void SetupStoredProcedures(string ConnectionString, Database Temp)
 {
     string Command = "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures";
     using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
     {
         try
         {
             Helper.Open();
             Helper.ExecuteReader();
             while (Helper.Read())
             {
                 string ProcedureName = (string)Helper.GetParameter("NAME", "");
                 string Definition = (string)Helper.GetParameter("DEFINITION", "");
                 Temp.AddStoredProcedure(ProcedureName, Definition);
             }
         }
         catch { }
         finally { Helper.Close(); }
     }
     foreach (StoredProcedure Procedure in Temp.StoredProcedures)
     {
         Command = "SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE [email protected] AND (sys.systypes.xusertype <> 256)";
         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
         {
             try
             {
                 Helper.Open();
                 Helper.AddParameter("@ProcedureName",128, Procedure.Name);
                 Helper.ExecuteReader();
                 while (Helper.Read())
                 {
                     string Type = (string)Helper.GetParameter("TYPE", "");
                     string Name = (string)Helper.GetParameter("NAME", "");
                     int Length = int.Parse(Helper.GetParameter("LENGTH", 0).ToString());
                     if (Type == "nvarchar")
                         Length /= 2;
                     string Default = (string)Helper.GetParameter("DEFAULT VALUE", "");
                     Procedure.AddColumn<string>(Name, Type.TryTo<string,SqlDbType>().ToDbType(), Length, Default);
                 }
             }
             catch { }
             finally { Helper.Close(); }
         }
     }
 }

 /// <summary>
 /// Sets up the functions
 /// </summary>
 /// <param name="ConnectionString">Connection string</param>
 /// <param name="Temp">Database object</param>
 private static void SetupFunctions(string ConnectionString, Database Temp)
 {
     string Command = "SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'";
     using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
     {
         try
         {
             Helper.Open();
             Helper.ExecuteReader();
             while (Helper.Read())
             {
                 string Name = (string)Helper.GetParameter("NAME", "");
                 string Definition = (string)Helper.GetParameter("DEFINITION", "");
                 Temp.AddFunction(Name, Definition);
             }
         }
         catch { }
         finally { Helper.Close(); }
     }
 }

 /// <summary>
 /// Gets the tables for a database
 /// </summary>
 /// <param name="ConnectionString">Connection string</param>
 /// <param name="Temp">The database object</param>
 private static void GetTables(string ConnectionString, Database Temp)
 {
     string Command = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
     using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
     {
         try
         {
             Helper.Open();
             Helper.ExecuteReader();
             while (Helper.Read())
             {
                 string TableName = (string)Helper.GetParameter("TABLE_NAME", "");
                 string TableType = (string)Helper.GetParameter("TABLE_TYPE", "");
                 if (TableType == "BASE TABLE")
                 {
                     Temp.AddTable(TableName);
                 }
                 else if (TableType == "VIEW")
                 {
                     Temp.AddView(TableName);
                 }
             }
         }
         catch { }
         finally { Helper.Close(); }
     }
 }

 /// <summary>
 /// Checks if something exists
 /// </summary>
 /// <param name="Command">Command to run</param>
 /// <param name="Name">Name of the item</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 private static bool CheckExists(string Command, string Name, string ConnectionString)
 {
     bool Exists = false;
     using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
     {
         try
         {
             Helper.Open();
             Helper.AddParameter("@Name",200, Name);
             Helper.ExecuteReader();
             if (Helper.Read())
                 Exists = true;
         }
         catch { }
         finally { Helper.Close(); }
     }
     return Exists;
 }

 /// <summary>
 /// Updates a database (only adds new fields, tables, etc. does not delete old fields)
 /// </summary>
 /// <param name="DesiredDatabase">The desired structure of the database</param>
 /// <param name="CurrentDatabase">The current database structure</param>
 /// <param name="ConnectionString">Connection string to the database</param>
 public static void UpdateDatabase(Database DesiredDatabase, Database CurrentDatabase, string ConnectionString)
 {
     if (CurrentDatabase == null)
     {
         CreateDatabase(DesiredDatabase, ConnectionString);
         return;
     }
     string Command = BuildCommands(DesiredDatabase, CurrentDatabase);
     string[] Splitter = { "\n" };
     string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
     using (SQLHelper Helper = new SQLHelper("", ConnectionString, CommandType.Text))
     {
         try
         {
             Helper.Open();
             Helper.BeginTransaction();
             for (int x = 0; x < Commands.Length; ++x)
             {
                 Helper.Command = Commands[x];
                 Helper.ExecuteNonQuery();
             }
             Helper.Commit();
         }
         catch { Helper.Rollback(); throw; }
         finally { Helper.Close(); }
     }
 }