Wednesday, October 9, 2013

SQL Query to get the database size in MySQL

The following query will returns the the database name and corresponding database size in MySQL

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ; 

Import large SQL File into MySQL Server in XAMP / WAMP

To import a large SQL file into MySQL Server in WAMP, copy the SQL file into "C:\wamp\bin\mysql\mysql5.5.24\bin", and navigate to the "C:\wamp\bin\mysql\mysql5.5.24\bin" in the command line window, then in the command line enter:

mysql -u [username] -p [DatabaseName] < [SQLFileName]

for example, is the [username] is root and [DatabaseName] is mixertruck, and the [SQLFilename] is mixertruck.sql, then the command line is 

mysql -u root -p mixertruck < mixertruck.sql

if the mysql is on a remote server, then use the following command line:

mysql -u [username] -h [HostName] -p [DatabaseName] < [SQLFileName]

Read very large text file


The Large Text File Viewer is a very good tool to read very large file such as very large SQL file, it can be downloaded from:


Use MS SQL Server Management Studio library to import a large SQL file in C#

Suppose you have a SQL File that needs to be imported into a MS SQL Database Server using C#, firstly import the following libraries as references in your C# project from the folder "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies"


  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.SqlEnum
Next use the following C# codes to import the SQL file

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;

private static void RunUT_ImportSQLFile()
{
 string script = "";

 string filepath = @"D:\temp\mixertruck.sql";

 if (!File.Exists(filepath))
 {
  Console.WriteLine("File [{0}] does not exist!", filepath);
  return;
 }

 StringBuilder script_sb = new StringBuilder();
 using (StreamReader reader = new StreamReader(filepath))
 {
  script = reader.ReadToEnd();
 }

 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
 sb.DataSource = @".\SQLEXPRESS";
 sb.InitialCatalog = "mixertruck";
 sb.UserID = "[username]";
 sb.Password = "[password]";
 string connection_string = sb.ToString();
 Console.WriteLine(connection_string);
 try
 {
  Console.WriteLine("Start importing data from: {0}", filepath);
  using (SqlConnection conn = new SqlConnection(connection_string))
  {
   Server server = new Server(new ServerConnection(conn));
   server.ConnectionContext.ExecuteNonQuery(script);
  }
  Console.WriteLine("Completed importing data from: {0}", filepath);
 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
 }
}


If the SQL file is really large the line "script=reader.ReadToEnd()" will throw memory exception, an alternative is as follows:
public static void RunUT_ImportLargeSQLFile()
{
 string filepath = @"D:\temp\mixertruck.sql";

 if (!File.Exists(filepath))
 {
  Console.WriteLine("File [{0}] does not exist!", filepath);
  return;
 }

 StringBuilder script_sb = new StringBuilder();
 string line = null;
 string query = null;
 

 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
 sb.DataSource = @".\SQLEXPRESS";
 sb.InitialCatalog = "mixertruck";
 sb.UserID = "[username]";
 sb.Password = "[password]";
 string connection_string = sb.ToString();
 int query_count=queries.Count;
 try
 {

  using (SqlConnection conn = new SqlConnection(connection_string))
  {
   Server server = new Server(new ServerConnection(conn));
   
   using (StreamReader reader = new StreamReader(filepath))
   {
    while ((line = reader.ReadLine())!= null)
    {
     script_sb.AppendLine(line);
     if (line.Contains(";"))
     {
      query = script_sb.ToString();
      script_sb.Clear();
      server.ConnectionContext.ExecuteNonQuery(query);
     }
    }
   }
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine("Error executing: {0}\r\n{1}", query, ex.ToString());
 }
}

Use SqlConnectionStringBuilder

The following code shows how to use SqlConnectionStringBuilder to conveniently construct the connection string to connect to MS SQL Server (Default SQL Server instance is assumed)


public static void RunUT_Connection()
{
 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
 sb.DataSource = @".\SQLEXPRESS";
 sb.InitialCatalog = "mixertruck";
 sb.UserID = "[username]";
 sb.Password = "[password]";
 string connection_string = sb.ToString();
 Console.WriteLine(connection_string);
 try
 {
  using (SqlConnection conn = new SqlConnection(connection_string))
  {
   conn.Open();

   conn.Close();
  }
 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
 }
}

Using command line to import a large SQL file into SQL Server Express 2008

Suppose you have a large SQL file (file with extension *.sql) that you want to import into a database in SQL Server Express 2008. you can use the following command line:

sqlcmd -S [ServerName]\[ServerInstanceName] S -d [DatabaseName] -i [SQLFilePath]

For example, suppose the SQL file is located at D\temp\mixertruck.sql, and you use default server instance .\SQLEXPRESS which already has a database mixertruck, then the command line is as follows:

sqlcmd -S .\SQLEXPRESS -d mixertruck -i D:\temp\mixertruck.sql

Tuesday, October 8, 2013

How to install SQL Server Express 2008

Follow the following link:
http://www2.blackthornesw.com/discus/messages/932/88726.html

If you encounter error such as "Object reference not set to an instance of an object" during installation or uninstallation, the most probable cause would be you have installed earlier version of SQL Server Express (e.g. 2000, 2005, etc). To solve the problem, uninstall earlier version of the SQL Server Expression version, then install the SQL Server Express 2008. If you need those earlier version of SQL Server Expression, you can install them after you install SQL Server Express 2008.