C# .NET Execute SQL Script WITHOUT SMO

Hi, SMO objects may be overkill for this task...

ADO objects are very reliable now, and can be installed using MDAC if required. but usually are already installed as service packs

Add a reference to MSADO 2.x objects, and then go from there using connection string etc  on Connection and Recordset object, or simply executing scripts on the Connection object

SQL 2008 feature pack has the Redistributable components for Microsoft® SQL Server® 2008 R2 that you need.

http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52

Not used ADO before... Any chance you could provide a quick sample as to how to run a script using ADO as per my SMO example?

Hi CarlvanEijk,

Yes  ihave seen and downloaded the redistributable and if I still had Installshield I doubt it would be a problem to install the correct one.  Unfortunately I am stuch with the Visual Studio Installation project and I am not sure if/how I could install the redistributable from this if required.  

Do you know how to include the SMO redistributable into an Installation project and get it to run if requiired?

Sorry Chris, I'm new to c# and VS.

with the installer config, can you not set the SQL 2008 feature pack as a prerequisite and have it install manually on the client?

Yes - this is the first VS Installer I have had to do - I always used InstallShield before.  Irt would seem that you can't add this as a pre-requisite - but I could be wrong!

on the remote sql server you can generate your database using OSQL
OSQL is the command line utility of SQL to connect to remote databases using command prompt and then query them or update them and do other functions
http://support.microsoft.com/kb/325003

Hi ragi0017:

Yes - but I'm installing the application on a PC which probably does not have SQL Server installed - this is a commercial product and so I  need a product which will work on any PC but still able to create the database.  If I install the SMO redistributable then it works fine - its just how best to do this or wherther I can replace SMO with anything else as all i am doing is executing a few scripts from within my .NET 3.5 application.

i saw your problem statement and it said that create on some remote database so i assumed that the machine will have sql installed

Once your script has connected to the server, all you need to do to create a database is to run sql statement CREATE DATABASE. The installation must have permissions to run it on the server, but  otherwise it's the same statement as any other, nothing special.

Sorry - I obviously have not explained well enough - My application will execute a .sql file for a few reasons - to add stored procedures and create indexes as well as running upgrade scripts in future versions.  My application definately has to be able to execute a .sql script.

My question is whether I have to use SQL Management Objects to do this or whether there is a better (.net) way of executing a .sql file on a PC which does not actually have SQL Server installed on it.

If SMO is the best (only) way to do this - how can I distributes and install SMO from a Visual Studio 2008 installation project?

> My question is whether I have to use SQL Management Objects to do this

No, you don't need SMO objects in order to issue SQL command CREATE DATABASE.

> or whether there is a better (.net) way of executing a .sql file

Depends on your criteria for the "better". If your installation is MSI-based, then I think, the best way is by using vbscript or javascript custom action - not .Net. The reason is in the fact that Windows Installer does not fully support custom actions with managed code - see http://robmensching.com/blog/posts/2007/4/19/Managed-Code-CustomActions-no-support-on-the-way-and-heres for details.

With vbscript, this is as easy as

set c=createobject("adodb.connection")
c.open <connection string>
c.execute "create database mydb"

If <connection string> connects with the credentials that on the server have permissions to create databases, then it will work.

Serious installation-authoring tools usually offer support of creating the database.

...and, answering your question, the same vbscript can then run all your other sql commands - create tables etc. etc.

...another note I'd like to add is that with sql server 2005 and higher, usually installation authors choose not to depend on some other server, local or remote, but rather they install sql express on the local machine (an individual instance) and then attach database file that already has all objects the application needs. This gives the best independence of the product. These releases of SQL server have been built with exactly this model of deployment in mind, and they fully support it, i.e. they are designed to coexist with other versions of sql server and other instances on the same machine.

Thanks for the comments -

As I mentioned earlier, I am limited to the Visual Studio 2008 installation package which I don't think would support vbscript and anyway I really want all of the database creation/upgrade code in the .NET application not in the installer.

As this is a multi-use product I expect it to be installed on multiple PCs all connecting to a single central database hence why I have not shipped SQL Server Compact Editiion with the app.  That approach is great for single user apps but not multi-user.

The underlying question of using SMO from my .NET code has really gone unanswered apart from the first reply from gmleeman - maybe the use of ADO rather than SMO will help as it certainly avoids using SMO - now why didn't Microsoft just include SMO as part of the .NET framework!

Hi ChrisMDrew, Sorry tried to paste sample code but my damn MSVS 2010 new fancy software has an UNKNOWN ERROR...and now my PC with the source code is turned off with my Dad asleep in the same room....Hahaha parody of disasters....So, sorry - I digress...I will have to code from memory or google whichever looks better :)  Anyway ... It seems MS advise NOT to use ADO with .NET, although I have tweaked it to work fine for many .NET apps.  They recommend ADO.NET instead. Anyway - lets have a look:

- Create a new Visual C# .NET Windows application project.
- On the Project menu, click Add Reference.
- Click the COM tab. Click Microsoft ActiveX Data Objects 2.X Library.

Code:

private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();

Then you can simply EXECUTE a sql command by

cn.Execute("CREATE DATABASE XYZ");

without even using the RecordSet object

Please investigate this as there are more appropriate methods when you use ADO.NET like:
ExecuteNonQuery
ExecuteScalar
etc .... give them a google....a bit more tedious but will definitely cover all needs

This should give you access to ADO or ADO.NET (I've mixed them up a little here - sorry) on any machine (running .NET framework) without installing anything more complex than MDAC

OOps, you may want to open the Connection first before using Execute...and use a try...catch loop etc:

     cn.Open("Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=;<strong password>";);

Then close it like this - which can wait until the very very end (of the program) if you like:

     cn.Close();

SqlConnection connection = new SqlConnection(_dbConnection.ConnectionString);
sqlcommand command = new sqlcommand (script,connection);
sqlcommand.ExecuteNonQuery();

This script is using System.Data.SqlClient, which is standard component of .net framework. It can include CREATE DATABASE among other statements.

Still the same problem as before - I use SqlConnection et-al extensively in my application - I don't want to embed all of the database creation/population code in my app as that makes it very difficult to upgrade.  I want to be able to use .SQL scripts which I can update as necessary.  I probably have 150-200 stored procedures written!

The original question still stands - how/can I execute an SQL script held in an external .sql file without having to resort to the SMO objects?

If I have to use SMO - what is the best way to distribute SMO in a ciommercial piece of software - i.e. I can't ask the user to download various files and install them as they are not PC literate enough to do that - these are end-users

I've ended up checking for SMO being installed and if not running some additional code to install it and its dependancies.  As mentioned at the start I needed a way either to be able to run an SQL script without SMO or find a way of installing SMO.  None of the above answers really helps with this.

We made every possible attempt to explain that SMO is unnecessary, and explained how to code the script that will do the job, complete with several examples of code both in vbscript and in C#. The original question #1 was directly answered in http:#33808272, and then an example of the required script in http:#33819924 .

Suggestion: accept http:#33808272

(the above comment was supposed to be regular objection, not an administrative comment - sorry)