Separate Code from SQL queries.

[maps]

Hi, i have been building my application with SQL queries mixed with the code.
recently i got a challenge. i recieved .sql files(MS SQL Server Query Files) that had queries to CREATE table and INSERT data SQL statements in them. i am meant to write code(for some stand alone app) and use them as they are. i was instructed to also separate my SQL queries and the code.

i dont know where to start since i have been using client/server architecture. with a connection string to the server.
How can i go about this? is there a document/tutorial some where i can read?
Attached herewith is some of the .sql files. take a look, to get a good pictute.

Thanks

[Techno]

depends totally on your app and design. something we cannot help with at all really. its a decision thing for you. i mean, where are you intending on putting the code? Are the create/insert statements part of the execution functionality in your client standalone app? or are you meant to just update the database once? if its the last one, then just put it in the query editor and execute!

[jmcilhinney]

To execute one of those SQL files you can call File.ReadAllText to get the contents as a single String, assign that to the CommandText of a DbCommand and call ExecuteNonQuery.

As far as separating your data access code from the UI, you should do some reading on n-tier design.

[maps]

First up, i am sorry for postin code in C#. before i knew it, my post was in the VB.NET forum and i coudnt change it. But i also believe that i can get help here.

I have this code below but it gives me an exception. That it has "failed to connect to the server".

using System.Data;
using System.Drawing;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.IO;//For the existance of the File.
using System.Windows.Forms;

       static private string strSQLConn = "" +
               "Data Source=SQLEXPRESS;" +
               "Initial Catalog=dbIncidents;" +
               "Integrated Security=true;";

        private void createTables1()
        {

            using (SqlConnection myConn = new SqlConnection(strSQLConn))
            {
                FileInfo filePath = new FileInfo("tables.sql");
                string sqlScript = filePath.OpenText().ReadToEnd();
                Server svr = new Server(new ServerConnection(myConn));
                svr.ConnectionContext.ExecuteNonQuery(sqlScript);
            }

        }


        private void button1_Click(object sender, EventArgs e)
        {
            createTables1();
        }

Below is another link i have looked at to accomplish the same task but i am not getting through it!

http://msdn.microsoft.com/en-us/library/ms811086.aspx

Any help will be appreciated.

[Techno]

try changing the datasource connection string to this:

static private string strSQLConn = "" +
"Data Source=.\SQLEXPRESS;" +
"Initial Catalog=dbIncidents;" +
"Integrated Security=true;";

if the database is stored on the local computer.... otherwise, if its a different computer on the same network, specify the computername\SQLExpress instance name, but also enable remote connections so it will accept incoming calls from remote computers on the network

im not entirely sure, cannot remember as its been a while on how the SMO works, but open the connection to the server first:

myConn.Open();

// close:

myConn.Close();

[maps]

C# doesnt allow *\SERVER.

i opened and closed the connection then it brought up a message that
"this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections."

How can i enable remote connections on SQLEXPRESS. I have a Management Studio installed.

[jmcilhinney]

You're on the wrong track. Techno has given you the right advice. As a C# developer you should know that '\' is the escape character and if you want to use it in string literals you have to either escape it:

static private string strSQLConn = "" +
               "Data Source=.\\SQLEXPRESS;" +
               "Initial Catalog=dbIncidents;" +
               "Integrated Security=true;";

or use a verbatim string literal:

static private string strSQLConn = "" +
               @"Data Source=.\SQLEXPRESS;" +
               "Initial Catalog=dbIncidents;" +
               "Integrated Security=true;";

Just one example of why you should post in the most appropriate forum in the first place, or ask a moderator to move your thread if you do post in the wrong forum.

Note that that error message is just a generic SqlException message. If you want the actual message for the error that occurred you have to get each SqlError from the exception's Errors collection.

Finally, what's the empty string for?

       static private string strSQLConn = "" +
               @"Data Source=.\SQLEXPRESS;" +
               "Initial Catalog=dbIncidents;" +
               "Integrated Security=true;";

[maps]

Thanks JMC!
Apologies for mis-appropriating the post.Mr Moderator, please move it.
On fixing the connection string, it gives me this Exception "An exception occurred while executing a Transact-SQL statement or batch."

And the stack trace is

Microsoft.SqlServer.Management.Common.ExecutionFailureException was unhandled
  Message="An exception occurred while executing a Transact-SQL statement or batch."
  Source="Microsoft.SqlServer.ConnectionInfo"
  StackTrace:
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
       at BatchDDLSQLExecution.Form1.createTables1() in C:\Documents and Settings\KRSK\Desktop\_Projects 3\BatchDDLSQLExecution\BatchDDLSQLExecution\Form1.cs:line 40
       at BatchDDLSQLExecution.Form1.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\KRSK\Desktop\_Projects 3\BatchDDLSQLExecution\BatchDDLSQLExecution\Form1.cs:line 49
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at BatchDDLSQLExecution.Program.Main() in C:\Documents and Settings\KRSK\Desktop\_Projects 3\BatchDDLSQLExecution\BatchDDLSQLExecution\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

i also enabled remote connections on the SQL SERVER but it never helped.

[jmcilhinney]

If an error occurred while executing the SQL then obviously a connection was made, so remote connections is not the issue. It couldn't be an issue anyway, given that you're connecting to your own machine.

I'm confused as to why you're using SMO. You should just be using ADO.NET. Create an SqlCommand and assign the contents of your file to its CommandText property, then call its ExecuteNonQuery method. As long as your SQL code is valid, which I suspect is not the case, then it will work fine. If you still have issues then please post your SQL code, and I don't mean attach a file.

[maps]

Thanks JMC, you were right. It was the SQL CODE that had problems. I had to create some dummy tables and create sql scripts then use that.
i also changed the code to this but i am not pretty sure whether its precise since i thout of looping through every the sql command using a "foreach loop" but i failed.
Here is what is giving me results. correct me if need be.

private void createTables2()
        {
            using (SqlConnection myConn = new SqlConnection(strSQLConn))
            {
                        
                try
                {
                    myConn.Open();
                        SqlCommand cmdSQL = myConn.CreateCommand();
                        //cmdSQL.CommandText = File.OpenText ("tables2.sql").ReadToEnd();
                        cmdSQL.CommandText = File.ReadAllText("tables2.sql");
                        cmdSQL.ExecuteNonQuery();                                       
                }
                catch
                {
                    
                    throw;
                }
                finally
                {
                    myConn.Close();
                    myConn.Dispose();
                    
                }                
                
            }
        }

[jmcilhinney]

That's cool. Your exception handling is pointless though. The 'finally' block is redundant because your 'using' block will close and dispose the connection. Your catch block is pointless because you don't do anything but rethrow. If you want the exception to bubble up then just don't catch it.

[maps]

Hi, i cant understand why this wont work for an access db. When i trie it out with two lines on sql strings in the .sql file, it gives me an sql exeption that there is a syntax error in the sql.
HOWEVER, with one sql string, it passes through and creates the table.

private void createTables2()
        {
            using (OleDbConnection myConn = new OleDbConnection(strAccessConn2))
            {
                OleDbCommand cmdSQL = new OleDbCommand(File.ReadAllText("tables1.sql"), myConn);
                try
                {
                    myConn.Open();
                    cmdSQL.ExecuteNonQuery();
                }
                    /*
                catch(OleDbException exSQL)
                {
                    MessageBox.Show(this,exSQL.Message ,"SQL Error!");
                    throw;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(this, ex.Message, "General Error!");
                    throw;
                }*/
                finally
                {
                    cmdSQL.Dispose();
                }
                
            }

sql strings

CREATE TABLE table1(id1 CHAR(20));
CREATE TABLE table2(id2 CHAR(20));

[jmcilhinney]

The Jet OLEDB provider doesn't support multiple statements. You'd have to execute two commands with one statement each or, more likely, the same command multiple times and change the CommandText in between.