VB.NET and .SQL File

Topics: Database Publishing Wizard
Mar 28, 2007 at 3:32 PM
Hey,

I have create a .sql file with the informations about my database. Now I need a small help. How can I import this file in my desktop application? I want to create a database from my application

I have tryed some ways but they didn´t work.

Have someone a sample in VB.NET 2005 for me how I can read the SQL-File (with complex stored procedures and views) in my application and execute it on the local/connected SQL2005-Server?

Thanks
MyKey0815
Mar 29, 2007 at 6:56 PM

MyKey0815 wrote:
Hey,

I have create a .sql file with the informations about my database. Now I need a small help. How can I import this file in my desktop application? I want to create a database from my application

I have tryed some ways but they didn´t work.

Have someone a sample in VB.NET 2005 for me how I can read the SQL-File (with complex stored procedures and views) in my application and execute it on the local/connected SQL2005-Server?

Thanks
MyKey0815


Hi MyKey0815 -

Here is a sample of how to do this using C#. It should be easy to convert to VB.NET.

 
        SqlConnection conn = null;                   
        try
        {
            Console.WriteLine(String.Format("Opening file {0}", fileName));
            
            // read file
            using (StreamReader sr = new StreamReader(new FileStream(fileName, FileMode.Open)))
            {
                Console.WriteLine("Connecting to SQL Server database...");
                
                // Create new connection to database
                conn = new SqlConnection(connectionString);               
                
                conn.Open();
 
                while (!sr.EndOfStream)
                {
                    StringBuilder sb = new StringBuilder();
                    SqlCommand cmd = conn.CreateCommand();
                    
                    while (!sr.EndOfStream)
                    {
                        string s = sr.ReadLine();
                        if (s != null && s.ToUpper().Trim().Equals("GO"))
                        {
                            break;
                        }
                        
                        sb.AppendLine(s);
                    }
 
                    // Execute T-SQL against the target database
                    cmd.CommandText = sb.ToString();
                    cmd.CommandTimeout = timeout;
 
                    cmd.ExecuteNonQuery();
                }
 
            }
            Console.WriteLine("T-SQL file executed successfully");
        }
        catch (Exception ex)
        {
            Console.WriteLine(String.Format("An error occured: {0}", ex.ToString()));
        }
        finally
        {
            // Close out the connection
            //
            if (conn != null)
            {
                try
                {
                    conn.Close();
                    conn.Dispose();
                }
                catch (Exception e)
                {
                    Console.WriteLine(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));
                }
            }
        }                       
 
 

Thanks,
alex