 |
|
JScript.NetTutorial #7
At last! We finally get to databases. Hurrah! Each time a new version of a programming language comes out, they change the way you deal with databases. We've had DAO, ADO and now we get ADO.Net which hopefully will be the only one we need from now on. So all we need now is some code to create a database in ADO.Net. Ah...ummm...well it seems that you can't. Sorry. No, its true. You cannot create Access databases in ADO.net although you can create them fine on SQLServer. OK...I'll spill the beans...it is possible and this is how you do it:-
import System;
import ADOX;
public class Test
{
public static function Main() : void
{
var cat : ADOX.CatalogClass = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
Console.WriteLine("Database Created Successfully");
cat = null;
}
}
Test.Main();
You have to take a little sideways move to something called ADOX which is a COM object called ADO Ext. 2.7 for DDL Security which is a bit of a mouthful. So having found our reference, all we have to do is compile it right? Um...well...no. It can't be compiled on the command line like we did before. The only way I hve found to do this is use an IDE (I used Borland C# Builder but you could use any other) to create a reference to the above object and it then creates a interop.ADOX.dll which can then be used on the command line as follows:-
jsc /r:interop.adox.dll %1.js
which means the dll has to be in the same directory as the .js file and it compiles fine. Phew. Now we can create an access database. But has it actually been created? You could look in the current directory of course but we would perhaps like to be able to do that from within a program so here is a program that seeks to find out if a database has been created:-
import System;
import System.Data;
import System.Data.OleDb;
public class Test
{
public static function Main() : void
{
var answer : String=null;
var strSQL : String=null;
var connectionString : String = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = newmdb.mdb";
var conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Console.WriteLine("Connected to data source");
}
catch (e)
{
Console.WriteLine("Failed to connect to data source");
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
Console.WriteLine("Data source closed");
}
Console.WriteLine("Press a key to continue...");
answer = System.Console.ReadLine();
}
}
Test.Main();
This is where our error code comes in because if the database hasn't been created then we want to know about it without the program crashing terminally. Note I've added some code to get a keypress at the end in case the program has been launched from Windows Explorer. Now we want to create a table inside it. A table is much like an array holding information in boxes which can be added to, overwritten or deleted. So how to create a table. Is it as fiddly as creating a database? Luckily no. Take a look:-
import System;
import System.Data;
import System.Data.OleDb;
public class Test
{
public static function Main() : void
{
var answer : String=null;
var strSQL : String=null;
var connectionString : String = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = newmdb.mdb";
var conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Console.WriteLine("Connected to data source");
strSQL = "CREATE TABLE fone (strName varchar(20)," +
"strNumb varchar(20))";
var cmd = conn.CreateCommand();
cmd.CommandText=strSQL;
cmd.ExecuteNonQuery();
}
catch (e)
{
Console.WriteLine("Failed to connect to data source");
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
Console.WriteLine("Data source closed");
}
Console.WriteLine("Press a key to continue...");
answer = System.Console.ReadLine();
}
}
Test.Main();
Don't look so worried - it sin't that bad. First we need to include a reference to the OLEDB library which is what ADO.Net uses to do all the hard work. Then we are into Main() and setup two variables and assign them as containing a null character (effectively empty) and then comes this big long string:-
var connectionString : String = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source = newmdb.mdb";
This is just saying that we want to use a database called newmdb.mdb and the service we need to be able to open it (OLEDB). Next we create a connection to the database:-
var conn = new OleDbConnection(connectionString);
Then we get into the try code where we attempt to open a connection the database and if all is well doesn't create an error. Now to ask it to allow us to create a table:-
strSQL = "CREATE TABLE fone (strName varchar(20), strNumb varchar(20))";
This involves using a well known database language called SQL which in this case is asking to create a table in the target database with the name fone (it is a simple phonebook app) with two fields of name and number (I'm using a string to handle the phone number as there is no maths to be calculated using it). Again, if no errors are generated then the table will be created. The actual SQL command is invoked by ExecuteNonQuery which is used when we don't expect to get any information back from the SQL command (we'll get to those later). All being well, we get a connection closed message and the program ends. How do we know it has been created? Well, we could try and write some data to it:-
import System;
import System.Data;
import System.Data.OleDb;
public class Test
{
public static function Main() : void
{
var answer : String=null;
var strSQL : String=null;
var connectionString : String = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = newmdb.mdb";
var conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Console.WriteLine("Connected to data source");
strSQL = "INSERT INTO fone (strName, strNumb) VALUES ('Dave', '123456')";
var cmd = conn.CreateCommand();
cmd.CommandText=strSQL;
cmd.ExecuteNonQuery();
}
catch (e)
{
Console.WriteLine("Failed to connect to data source");
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
Console.WriteLine("Data source closed");
}
Console.WriteLine("Press a key to continue...");
answer = System.Console.ReadLine();
}
}
Test.Main();
There isn't much different here apart from the SQL statement entering values into the table. This code could easily be a class or function where the SQL is passed to it as well as the data to be inserted. Again, if there are no errors, all will be well. But do we know if the data is in there or not? More code:-
import System;
import System.Data;
import System.Data.OleDb;
public class Test
{
public static function Main() : void
{
var answer : String=null;
var strSQL : String=null;
var dataTable : DataTable;
var totalRec : int=0;
strSQL = "SELECT * FROM fone";
var i : int = 0;
var connectionString : String = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = newmdb.mdb";
var conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Console.WriteLine("Connected to data source");
var dataAdapter = new OleDbDataAdapter(strSQL, conn);
var ds = new DataSet();
dataAdapter.Fill(ds, "fone");
dataTable = ds.Tables["fone"];
totalRec = dataTable.Rows.Count;
Console.WriteLine("Total recs= " + totalRec);
for(i=0; i<totalRec; i++)
{
Console.WriteLine(dataTable.Rows[i]["strName"].ToString());
Console.WriteLine(dataTable.Rows[i]["strNumb"].ToString());
}
}
catch (e)
{
Console.WriteLine("Failed to connect to data source");
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
Console.WriteLine("Data source closed");
}
Console.WriteLine("Press a key to continue...");
answer = System.Console.ReadLine();
}
}
Test.Main();
This time because we are getting data out of the database then we use a dataadapter that then fills a dataset. We also use and SQL statement using SELECT * from fone where the asterisk means everything from the table fone. We create a dataset ds which is filled by the dataadapter from the fone table. Then we get the number of rows and use a loop to display all the data until there is no more. The prgram then finishes hopefully with no errors. Well, I think thats quite enough to be getting on with. Try inputting different data (or even the same date) or creating new tables. I'll finish up on databases next time and we might even get to do some real Windows GUI programs. Stay tuned...