Transact-SQL calls from within C#
-
I'm learning to deliver some additional functionality to the end user I'm going to have to envoke some transact-sql statements to detach and reattach my SQL-Server (MSDE) database. My C# application uses standard ADO.NET but I can't find any examples of Transact-sql statements be called from within a C# program. Any hints or an snippet of code on how I would do this from within a C# application? Thanks, cb
-
I'm learning to deliver some additional functionality to the end user I'm going to have to envoke some transact-sql statements to detach and reattach my SQL-Server (MSDE) database. My C# application uses standard ADO.NET but I can't find any examples of Transact-sql statements be called from within a C# program. Any hints or an snippet of code on how I would do this from within a C# application? Thanks, cb
-
I'm learning to deliver some additional functionality to the end user I'm going to have to envoke some transact-sql statements to detach and reattach my SQL-Server (MSDE) database. My C# application uses standard ADO.NET but I can't find any examples of Transact-sql statements be called from within a C# program. Any hints or an snippet of code on how I would do this from within a C# application? Thanks, cb
Something like the following should do it:
static void DetachPubsDB() { //Creates a connection string connStr = "provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;Initial Catalog=master; Network Library=dbmssocn"; OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); //Detaches the database OleDbCommand cmdDetach = new OleDbCommand("sp_detach_db", conn); cmdDetach.CommandType = CommandType.StoredProcedure; cmdDetach.Parameters.Add("dbname", "Pubs"); cmdDetach.ExecuteNonQuery(); //Attaches the database OleDbCommand cmdAttach = new OleDbCommand("sp_attach_db", conn); cmdAttach.CommandType = CommandType.StoredProcedure; cmdAttach.Parameters.Add("dbname", "Pubs"); cmdAttach.Parameters.Add("filename1", @"C:\Program Files\Microsoft SQL Server\Data\MSSQL\Data\pubs.mdf"); cmdAttach.Parameters.Add("filename2", @"C:\Program Files\Microsoft SQL Server\Data\MSSQL\Data\pubs_Log.ldf"); cmdAttach.ExecuteNonQuery(); } catch(Exception excp) { Console.WriteLine(excp.Message); } finally { if(conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } }The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Something like the following should do it:
static void DetachPubsDB() { //Creates a connection string connStr = "provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;Initial Catalog=master; Network Library=dbmssocn"; OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); //Detaches the database OleDbCommand cmdDetach = new OleDbCommand("sp_detach_db", conn); cmdDetach.CommandType = CommandType.StoredProcedure; cmdDetach.Parameters.Add("dbname", "Pubs"); cmdDetach.ExecuteNonQuery(); //Attaches the database OleDbCommand cmdAttach = new OleDbCommand("sp_attach_db", conn); cmdAttach.CommandType = CommandType.StoredProcedure; cmdAttach.Parameters.Add("dbname", "Pubs"); cmdAttach.Parameters.Add("filename1", @"C:\Program Files\Microsoft SQL Server\Data\MSSQL\Data\pubs.mdf"); cmdAttach.Parameters.Add("filename2", @"C:\Program Files\Microsoft SQL Server\Data\MSSQL\Data\pubs_Log.ldf"); cmdAttach.ExecuteNonQuery(); } catch(Exception excp) { Console.WriteLine(excp.Message); } finally { if(conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } }The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Thank you sooooooo much, I can hardly wait to give it a try! Why is it that finding a simple example such as the one you have furnished is so difficult? Thanks again! cb
Actually, it's not so bad. MSDN has lots of good articles on various facets of ADO.Net and if all else fails, there's Google. I would strongly recommend visiting Microsoft's ADO.Net Primer[^] if you plan to work with ADO.Net a lot. Most of the challenge that I've seen people suffer through with ADO.Net stems from their previous history with ADO 2.x (COM-based). The two ADO implementations share some terminology and concepts, but are largely different. Many experienced ADO 2.x developers find ADO.Net syntax more cumbersome than ADO 2.x and I suppose I agree to some extent. A very good place to start is by examining the ADO.Net object hierarchy. Take the time to read about each class (there really aren't that many, and there are only a handful that get used in most common database transactions). CodeProject is a great place to get information, but you have to have a mindset that you're prepared to explore and experiment - experience is the best teacher.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Actually, it's not so bad. MSDN has lots of good articles on various facets of ADO.Net and if all else fails, there's Google. I would strongly recommend visiting Microsoft's ADO.Net Primer[^] if you plan to work with ADO.Net a lot. Most of the challenge that I've seen people suffer through with ADO.Net stems from their previous history with ADO 2.x (COM-based). The two ADO implementations share some terminology and concepts, but are largely different. Many experienced ADO 2.x developers find ADO.Net syntax more cumbersome than ADO 2.x and I suppose I agree to some extent. A very good place to start is by examining the ADO.Net object hierarchy. Take the time to read about each class (there really aren't that many, and there are only a handful that get used in most common database transactions). CodeProject is a great place to get information, but you have to have a mindset that you're prepared to explore and experiment - experience is the best teacher.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
Agrreed and thank you. I have bought several ADO.NET books and am picking it up really quickly. I was doing pretty good until I needed to write an application using an MSDE DB on a distributable CD. In one sense since my application will be just referencing (reading) the DB my work is easy. The hardwork is in developing a 'setup' routine that installs MDAC, .NET, MSDE, my application and reference the data on the CD. This kind of through me into using transact-sql, something I've never needed to do before. I found plenty of examples of transact-sql statements and I've written qutie a few ExecuteNonQuery() routines but I couldn't find an example that put it all together. What you pointed out was that my commandtype = storedprocedure which I didn't think I needed. So with your help and my recently purchased "The Guru's Guide to Transact-SQL" I'll get to experimenting. Thanks again, cb