first of all, i'm total newbie to all this ASP.net stuff.
I got a DB where the employee from the it branch can write their duties into a table with different arrays like title, start time, end time etc. We decided do work with a MS Access DB, because it seems to be the easiest way to update by just opening the
database and the gui for the tables and then add a new topics.
The idea is now, that I build a Intranet-Homepage, where other employees can inform themselves about current changes in f.e. our merchandise management system. Something like a news site...
My question is now: is there any possibility that I somehow connect the cshtml page in WebMatrix to the Access database. I just want to read the different arrays dynamicly, it is not neccessary to write into the database.
yeah it definitly gives me a better overview :) but my problem is: my collegues open a Access Database... update their content and close MS Access... is there a possibility to update the SDF-Database that I use in WebMatrix from the Access Database, or automatically
sync it or the better option would be to directly use the Access Database.
You can connect to any kind of database, not just SQL Compact Edition. All you need to do is use the correct database connection/command and connection string. To connect to an Access database, you need to use the System.Data.OleDb namespace and ensure
that you include the provider in the connection string. For example:
This is something that I've been looking into for the past couple of days myself. They make it all nice and easy to open a connection to an SQL CE Database and access its data using C# Razor Syntax, but it would appear that if you want to open and access
any other type of database (Microsoft Access DB for instance; .accdb or .mdb file extension) you need to use ADO.NET - at least for the time being until (hopefully) they add 'native' Razor Syntax for accessing other types of databases other than SQL CE DBs.
It would probably be helpful to look into a book for an explantion of what ADO.NET is and how to use it, or at least research it a bit on the interwebs. I've just started looking deeper into my
Beginning Visual C# 2008 by Wrox that's been explaining it pretty well and giving excellent examples on how to open different types of database connections and access their data. Here's the example they have for
accessing an Access (.mdb) database with a console application.
using System;
using System.Data; // Use ADO.NET namespace
using System.Data.OleDb; // Use namespace for OLE DB .NET Provider
using System.Collections.Generic;
using System.Text;
static void Main(string[] args)
{
// Create connection object for Microsoft Access OLE DB Provider;
// note @ sign prefacing string literal so backslashes in path name;
// work.
OleDbConnection thisConnection = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind\nwind.mdb");
// Open connection object
thisConnection.Open();
// Create SQL command object on this connection
OleDbCommand thisCommand = thisConnection.CreateCommand();
// Initialize SQL SELECT command to retrieve desired data
thisCommand.CommandText =
"SELECT CustomerID, CompanyName FROM Customers";
// Create a DataReader object based on previously defined command object
OleDbDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
Console.WriteLine("\t{0}\t{1}",
thisReader["CustomerID"], thisReader["CompanyName"]);
}
thisReader.Close();
thisConnection.Close();
Console.Write("Program finished, press Enter/Return to continue:");
Console.ReadLine();
}
Taking this out of its Console Application context and adapting it to an ASP.NET application is fairly simple. You can place this code inside of a .cshtml file just like you would any C# code. So your basic .cshtml file:
@using System.Data @* My page worked just fine without this using statement, but I'll throw it in just in case.*@
@using System.Data.OleDb @* Be sure to include this using statement for the OLE DB .NET Data Provider namespace. *@
@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Database Test Page";
}
@{
// Create connection object for Microsoft Access OLE DB Provider.
// Again, notice the @ sign prefacing the string literal so that the slashes
// in the path name aren't interpreted as escape characters.
var thisConnection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =
C:\Documents and Settings\YOURUSERNAME\My Documents\My Web Sites\WebSite1\App_Data\database.mdb");
// Open Connection object
thisConnection.Open();
// Create SQL command object on this connection
var thisCommand = thisConnection.CreateCommand();
// Initialize SQL Select command to retrieve desired data
thisCommand.CommandText = "SELECT * FROM tableName";
// Create a DataReader object based on previously defined command object
OleDbDataReader thisReader = thisCommand.ExecuteReader();
<table>
<tr><td>COLUMN1:</td><td>COLUMN2:</td><td>COLUMN3:</td></tr>
@* Read() Advances the OleDbDataReader to the next record *@
@while(thisReader.Read())
{
<tr>
<td>@thisReader["COLUMN1"]</td> @* Print COLUMN1 field *@
<td>@thisReader["COLUMN2"]</td> @* Print COLUMN2 field *@ <td>@thisReader["COLUMN3"]</td> @* Print COLUMN3 field *@ @* And so on... *@ </tr>
}
</table>
}
From that code, all you really need to change is the path to your own particular database inside of the ConnectionString (@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\Your\File\Path\Here\databaseFile.mdb), the table name in the SELECT statement,
and which fields (columns) to print to the screen, and you should be good to go (at least for a basic select query). You can access every column in the result table from your query using the
@thisReader["Name_of_Column_Goes_Here"] command. Again, it would probably be a good idea to research ADO.NET a bit to learn more about accessing/manipulating databases with it; learn its syntax and methods/commands.
Hope that helps!
P.S. I typed out all the code myself, so I appologize for any possible spelling errors.
Awesome! I'm glad it worked out for you ^_^ Ah, 12.0 must be for newer Office software. The database I'm accessing is an Office 2007 file so 4.0 did the trick. Now to get to work implementing it further into my own project
Hello, I am very new to asp.net. I am facing the very same problem: I wanna connect to an access database with web matrix.
I tried this piece of code you posted but it did not work. My question is, do i need to download something extra in order to make this piece of code work? I have Web Matrix 2, IIS Express 7, Microsoft Access 2007. Do i need to download something more in
order for the code to work?
BoEneD
Member
39 Points
23 Posts
Connect to *.accdb Database with WebMatrix
Jan 31, 2012 07:38 AM|LINK
Hey folks,
first of all, i'm total newbie to all this ASP.net stuff.
I got a DB where the employee from the it branch can write their duties into a table with different arrays like title, start time, end time etc. We decided do work with a MS Access DB, because it seems to be the easiest way to update by just opening the database and the gui for the tables and then add a new topics.
The idea is now, that I build a Intranet-Homepage, where other employees can inform themselves about current changes in f.e. our merchandise management system. Something like a news site...
My question is now: is there any possibility that I somehow connect the cshtml page in WebMatrix to the Access database. I just want to read the different arrays dynamicly, it is not neccessary to write into the database.
Thx in advance :)
greetings
Benedikt
stevenbey
All-Star
16526 Points
3378 Posts
Re: Connect to *.accdb Database with WebMatrix
Jan 31, 2012 10:39 AM|LINK
Hi Benedikt
This video should help you.
http://stevenbey.com
Recursion: see Recursion
BoEneD
Member
39 Points
23 Posts
Re: Connect to *.accdb Database with WebMatrix
Jan 31, 2012 10:47 AM|LINK
yeah it definitly gives me a better overview :) but my problem is: my collegues open a Access Database... update their content and close MS Access... is there a possibility to update the SDF-Database that I use in WebMatrix from the Access Database, or automatically sync it or the better option would be to directly use the Access Database.
stevenbey
All-Star
16526 Points
3378 Posts
Re: Connect to *.accdb Database with WebMatrix
Jan 31, 2012 12:17 PM|LINK
You can connect to any kind of database, not just SQL Compact Edition. All you need to do is use the correct database connection/command and connection string. To connect to an Access database, you need to use the System.Data.OleDb namespace and ensure that you include the provider in the connection string. For example:
using(var conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\\mydb.mdb")) using(var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT * FROM [Table1]"; conn.Open(); using(var reader = cmd.ExecuteReader()) { ... } }http://stevenbey.com
Recursion: see Recursion
BoEneD
Member
39 Points
23 Posts
Re: Connect to *.accdb Database with WebMatrix
Jan 31, 2012 12:50 PM|LINK
oh okay.. but where do i put this code exactly? some websites told me to modify the _AppStart.cshtml, some said to directly in the specific file...
i tried it in the specific file, but it didn't even markup the syntax or recognized any code... ^^
sry I'n not experienced @ all with those kind of stuff.. :D cfml was way easier :D
cbergman
Member
14 Points
2 Posts
Re: Connect to *.accdb Database with WebMatrix
Jan 31, 2012 11:13 PM|LINK
This is something that I've been looking into for the past couple of days myself. They make it all nice and easy to open a connection to an SQL CE Database and access its data using C# Razor Syntax, but it would appear that if you want to open and access any other type of database (Microsoft Access DB for instance; .accdb or .mdb file extension) you need to use ADO.NET - at least for the time being until (hopefully) they add 'native' Razor Syntax for accessing other types of databases other than SQL CE DBs. It would probably be helpful to look into a book for an explantion of what ADO.NET is and how to use it, or at least research it a bit on the interwebs. I've just started looking deeper into my Beginning Visual C# 2008 by Wrox that's been explaining it pretty well and giving excellent examples on how to open different types of database connections and access their data. Here's the example they have for accessing an Access (.mdb) database with a console application.
using System; using System.Data; // Use ADO.NET namespace using System.Data.OleDb; // Use namespace for OLE DB .NET Provider using System.Collections.Generic; using System.Text; static void Main(string[] args) { // Create connection object for Microsoft Access OLE DB Provider; // note @ sign prefacing string literal so backslashes in path name; // work. OleDbConnection thisConnection = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind\nwind.mdb"); // Open connection object thisConnection.Open(); // Create SQL command object on this connection OleDbCommand thisCommand = thisConnection.CreateCommand(); // Initialize SQL SELECT command to retrieve desired data thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers"; // Create a DataReader object based on previously defined command object OleDbDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine("\t{0}\t{1}", thisReader["CustomerID"], thisReader["CompanyName"]); } thisReader.Close(); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }Taking this out of its Console Application context and adapting it to an ASP.NET application is fairly simple. You can place this code inside of a .cshtml file just like you would any C# code. So your basic .cshtml file:
@{ Layout = "~/_SiteLayout.cshtml"; Page.Title = "Some page title"; } @{ Place content here... }Becomes something like:
@using System.Data @* My page worked just fine without this using statement, but I'll throw it in just in case.*@ @using System.Data.OleDb @* Be sure to include this using statement for the OLE DB .NET Data Provider namespace. *@ @{ Layout = "~/_SiteLayout.cshtml"; Page.Title = "Database Test Page"; } @{ // Create connection object for Microsoft Access OLE DB Provider. // Again, notice the @ sign prefacing the string literal so that the slashes // in the path name aren't interpreted as escape characters. var thisConnection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Documents and Settings\YOURUSERNAME\My Documents\My Web Sites\WebSite1\App_Data\database.mdb"); // Open Connection object thisConnection.Open(); // Create SQL command object on this connection var thisCommand = thisConnection.CreateCommand(); // Initialize SQL Select command to retrieve desired data thisCommand.CommandText = "SELECT * FROM tableName"; // Create a DataReader object based on previously defined command object OleDbDataReader thisReader = thisCommand.ExecuteReader(); <table> <tr><td>COLUMN1:</td><td>COLUMN2:</td><td>COLUMN3:</td></tr> @* Read() Advances the OleDbDataReader to the next record *@ @while(thisReader.Read()) { <tr> <td>@thisReader["COLUMN1"]</td> @* Print COLUMN1 field *@ <td>@thisReader["COLUMN2"]</td> @* Print COLUMN2 field *@<td>@thisReader["COLUMN3"]</td> @* Print COLUMN3 field *@
@* And so on... *@
</tr> } </table> }
From that code, all you really need to change is the path to your own particular database inside of the ConnectionString (@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\Your\File\Path\Here\databaseFile.mdb), the table name in the SELECT statement, and which fields (columns) to print to the screen, and you should be good to go (at least for a basic select query). You can access every column in the result table from your query using the @thisReader["Name_of_Column_Goes_Here"] command. Again, it would probably be a good idea to research ADO.NET a bit to learn more about accessing/manipulating databases with it; learn its syntax and methods/commands.
Hope that helps!
P.S. I typed out all the code myself, so I appologize for any possible spelling errors.
BoEneD
Member
39 Points
23 Posts
Re: Connect to *.accdb Database with WebMatrix
Feb 01, 2012 06:09 AM|LINK
cbergman, you made my day! :D thx a lot :D just woke up and tried it right away.. worked PERFECTLY, i just had to change the
to
thx a lot! :D now i just need to find out, why he puts a div tag around the content of a memo field ^^
cbergman
Member
14 Points
2 Posts
Re: Connect to *.accdb Database with WebMatrix
Feb 01, 2012 03:51 PM|LINK
Awesome! I'm glad it worked out for you ^_^ Ah, 12.0 must be for newer Office software. The database I'm accessing is an Office 2007 file so 4.0 did the trick. Now to get to work implementing it further into my own project
mariammtariq
Member
2 Points
1 Post
Re: Connect to *.accdb Database with WebMatrix
Feb 28, 2013 07:38 PM|LINK
Hello, I am very new to asp.net. I am facing the very same problem: I wanna connect to an access database with web matrix.
I tried this piece of code you posted but it did not work. My question is, do i need to download something extra in order to make this piece of code work? I have Web Matrix 2, IIS Express 7, Microsoft Access 2007. Do i need to download something more in order for the code to work?
Please reply asap!
Thanks!