Differences

This shows you the differences between two versions of the page.

--- swdev:howto:create_database_using_firebird [2011/02/25 16:12]
smayr
+++ swdev:howto:create_database_using_firebird [2011/02/25 16:17] (current)
smayr [Run an embedded SQL Script against Firebird]
@@ Line 337: / Line 337: @@
 Source: [[http://codefrenzy.blogspot.com/2005/11/run-embedded-sql-script-against.html|Run an embedded SQL Script agains Firebird]]
-Run an embedded SQL Script against Firebird
+=== Run an embedded SQL Script against Firebird ===
 I had an idea that it would be great if my application could automatically create any database that it required.
@@ Line 344: / Line 344: @@
 Whilst investigating further, I found these useful links:
-Using FbConnectionStringBuilder
+  * [[http://www.dotnetfirebird.org/blog/2005/03/using-fbconnectionstringbuilder.html}Using FbConnectionStringBuilder]]
-Batch SQL/DDL Execution
+  * [[http://www.dotnetfirebird.org/blog/2005/03/batch-sqlddl-execution.html|Batch SQL/DDL Execution]]
 However, distributing a separate script file would allow a user to change or view the contents of the file.
@@ Line 352: / Line 352: @@
 I tested various routines and arrived at the following working example:
+<code csharp>
 void ButtonRunScriptClick(object sender, System.EventArgs e)
 {
-//specify the database name
+  //specify the database name
-string dbName = "test.fdb";
+  string dbName = "test.fdb";
-//instanciate a new connection stringbuilder
+  //instantiate a new connection stringbuilder
-FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
+  FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
-csb.Database = dbName;
+  csb.Database = dbName;
-csb.UserID = "SYSDBA";
+  csb.UserID = "SYSDBA";
-csb.Password = "masterkey";
+  csb.Password = "masterkey";
-csb.ServerType = 1;         //embedded server
+  csb.ServerType = 1;         //embedded server
-//instanciate a connection object
+  //instantiate a connection object
-FbConnection con = new FbConnection(csb.ToString());
+  FbConnection con = new FbConnection(csb.ToString());
-//check if the database exists
+  //check if the database exists
-if (System.IO.File.Exists(dbName) == false)
+  if (System.IO.File.Exists(dbName) == false)
-//create the database as it didn’t exist
+  //create the database as it didn’t exist
-FbConnection.CreateDatabase(csb.ToString());
+  FbConnection.CreateDatabase(csb.ToString());
-//run the script against the current connection
+  //run the script against the current connection
-RunScript("Test.Sql", con);
+  RunScript("Test.Sql", con);
 }
 void RunScript(string ScriptName, FbConnection connection)
 {
-//get a reference to the executing assembly
+  //get a reference to the executing assembly
-System.Reflection.Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();
+  System.Reflection.Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();
-//instanciate a textReader object initialised using a stream to the embedded resource
+  //instantiate a textReader object initialised using a stream to the embedded resource
-System.IO.TextReader textReader = new System.IO.StreamReader(assembly.GetManifestResourceStream(ScriptName));
+  System.IO.TextReader textReader = new System.IO.StreamReader(assembly.GetManifestResourceStream(ScriptName));
-FbScript script = new FbScript(textReader);
+  FbScript script = new FbScript(textReader);
-//parse the script
+  //parse the script
-script.Parse();
+  script.Parse();
-//open the connection
+  //open the connection
-connection.Open();
+  connection.Open();
-FbBatchExecution fbe = new FbBatchExecution(connection);
+  FbBatchExecution fbe = new FbBatchExecution(connection);
-foreach (string cmd in script.Results)
+  foreach (string cmd in script.Results)
-{
+  {
-//add each sql statement to the batch
+    //add each sql statement to the batch
-fbe.SqlStatements.Add(cmd);
+    fbe.SqlStatements.Add(cmd);
-}
+  }
-//execute the batch
+  //execute the batch
-fbe.Execute();
+  fbe.Execute();
-//close the connection
+  //close the connection
-connection.Close();
+  connection.Close();
 }
+</code>
 To test the above code, you will need to do the following:
-)     Download and install the 'Firebird ADO.Net Provider' from DotNetFirebird
+  # Download and install the 'Firebird ADO.Net Provider' from DotNetFirebird
-)     Once installed, create a new Windows Application and add a reference to your project for the FirebirdSql.Data.Firebird assebly loaded in the GAC.
+  # Once installed, create a new Windows Application and add a reference to your project for the FirebirdSql.Data.Firebird assebly loaded in the GAC.
-)     Copy & Paste the sample code into your project.
+  # Copy & Paste the sample code into your project.
-)     Add the following Using Directives:
+  # Add the following Using Directives: <code csharp>
 using FirebirdSql.Data.Firebird;
-using FirebirdSql.Data.Firebird.Isql;
+using FirebirdSql.Data.Firebird.Isql;</code>
+  # Download 'Embedded Firebird for Windows' from DotNetFirebird and extract ‘fbembed.dll’ and ‘firebird.msg’ from the zip archive. These files should be copied to your applications directory.
-)     Download 'Embedded Firebird for Windows' from DotNetFirebird and extract ‘fbembed.dll’ and ‘firebird.msg’ from the zip archive. These files should be copied to your applications directory.
+  # Create a text file named 'Test.Sql' to contain your Firebird SQL script and save it in your project directory. I would suggest including a ''CREATE TABLE'' statement such as: <code sql>
-)     Create a text file named 'Test.Sql' to contain your Firebird SQL script and save it in your project directory. I would suggest including a CREATE TABLE statement such as:
 CREATE TABLE CLIENTS (
-client_id integer not null,
+  client_id integer not null,
-firstname char(20),
+  firstname char(20),
-lastname char(20),
+  lastname char(20),
-PRIMARY KEY (client_id));
+  PRIMARY KEY (client_id)); </code>Note: The filename is case sensitive, if you choose a different filename you will need to amend the sample code to reflect this change.
+  # Add the file you created in Step 4 to your project and set the build action to Embedded Resource.
-Note: The filename is case sensitive, if you choose a different filename you will need to amend the sample code to reflect this change.
+  # Add a button to your form named buttonRunScript and check that its Click Event is set to the ButtonRunScriptClick Event Handler.
-)     Add the file you created in Step 4 to your project and set the build action to Embedded Resource.
-)     Add a button to your form named buttonRunScript and check that its Click Event is set to the ButtonRunScriptClick Event Handler.
-Hopefully, when you build the project and click the Run Script button, a file will be created in the applications directory named ‘TEST.FDB’.
+Hopefully, when you build the project and click the Run Script button, a file will be created in the applications directory named ''TEST.FDB''.
 Providing there were no errors during the applications execution the database should have been constructed according to the statements in the SQL script.
-There are several Database Admin Tools you can use to inspect/update and alter the database, one of which is FlameRobin which can also be downloaded for free.
+There are several Database Admin Tools you can use to inspect/update and alter the database, one of which is [[http://www.flamerobin.org|FlameRobin]] which can also be downloaded for free.