Rhyous

September 15, 2010

Using OSQL with Microsoft SQL databases

Filed under: Database,MS SQL Server — J. Abram barneck @ 2:54 pm

OSQL basic commands

Logging into a database with osql

c:\> osql -S [servername\instance] -U username [-P password]

Example1: Specifying the user and letting the command line prompt for a password.

c:\> osql -S Core\ldmsdata -U sa
password:

Example2: Specifying the user and the password.

c:\> osql -S ld87\ldmsdata -U sa -P pw

Example3: Using a trusted connection

c:\> osql -E

Show databases

1>
2>
select * from sysdatabases
go

Note: Or to see only the database Name row do the following:

1>
2>
select Name from sysdatabases
go

Creating a database

1>
2>
create database DatabaseName
go

Selecting a database

1>
2>
USE master
go

Drop a database

1>
2>
drop database DatabaseName
go

Show tables

1>
2>
1>
2>
USE DatabaseName
go
select * from INFORMATION_SCHEMA.TABLES
go

Note: Or to see only the Table_Name row do the following:

1>
2>
1>
2>
USE DatabaseName
go
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
go

Drop a table

1>
2>
1>
2>
USE DatabaseName
go
drop table TableName
go

Insert a row into a table

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName Values(“Column1value”,”Column2value”,”Column3value”)
go

Note: Or to insert by only providing values for a few columns and letting the other columns take the default values. This is useful when the first column is set to AUTO_INCREMENT.

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName (Col2, Col3) Values(“Column2value”,”Column3value”)
go

Update a value in row of a table

1>
2>
1>
2>
USE DatabaseName
go
UPDATE TableName set ColumnName=’NewValue’ where SomeColumn=’whereValue’
go

Drop a view

1>
2>
1>
2>
USE DatabaseName
go
drop view ViewName
go

Backup a database

1>
2>
BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT
go

You can do this at the command prompt with one single command:

c:\> osql -S ld87\ldmsdata -U sa -P pw -Q “BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT”

I hope this helps you.

More Information

For more information see the following website:

  1. Administering SQL Server Using osql
  2. osql Utility

March 4, 2010

What is and how do I install the Northwind database?

Filed under: C# (C-Sharp),MS SQL Server — J. Abram barneck @ 2:27 pm

The Northwind database is referenced online in sample code quite often. However, this renders the sample code useless to someone who doesn’t know what Northwind is or how to use it.

The Northwind database is basically just an example database that runs under SQL Server. This database is populated with data that represents an imaginary company’s sales data. It is a very common example database for SQL Server testing and sampling.

You might be wondering, what is SQL Server (though for your sake, I hope not). Well, SQL Server is Microsoft’s database software.

How do I know if SQL is installed?
If you have Visual Studio 2008 installed, you probably have SQL Server 2008 installed and you don’t even know it.

You can go to Add / Remove Programs and look for Microsoft SQL Server.

Or you can check for the services.

Or if you aren’t good with the GUI, you can open a command prompt and run this command to see if you have the SQL services:

C:\Users\UserName> sc query state= all |findstr SQL |findstr DISPLAY_NAME

DISPLAY_NAME: SQL Server (SQLEXPRESS)
DISPLAY_NAME: SQL Active Directory Helper Service
DISPLAY_NAME: SQL Server Agent (SQLEXPRESS)
DISPLAY_NAME: SQL Server Browser
DISPLAY_NAME: SQL Server VSS Writer

If you don’t see the services ouptut, you don’t have SQL Server Express installed. If you do have it, it is installed.

SQL Server is installed, now where do I get the Northwind Database?
Well, this was a struggle even for me. All the posts say the database creation script was installed with Visual Studio, but I sure don’t have any database creation scripts installed.

So I searched the web and Microsoft’s site for a while.

I finally found this link, but not until after about an hour of searching, hopefully this saves you some time.
http://www.codeplex.com/Wikipage?ProjectName=SqlServerSamples

So as of 3/4/2010, there was not “new” Northwind database, just and old one for SQL 2000. Which is fine because that old database is what most the sample code you find will be using.

I clicked on the Download link next to SQL Server 2000 Sample DBs.
I downloaded a ZIP file.
I extracted it.
I found the instnwnd.sql script among the extracted files.

How do I use the instnwnd.sql file to install the Northwind Database
This instnwnd.sql is nothing more than SQL script that will install the Northwind database for you. Well, you basically need your SQL server to run this script file and that is it.

If you have SQL Server Management Studio, just open it up and connect to your database, then File | Open the script and run it. But maybe you don’t know what SQL Server Management Studio is, let alone how to open it.

Sound easy right.

Well, everything sounds easy to some one who knows exactly how to do it, but if you don’t now how, it doesn’t sound easy. If you are among those that are hearing about this for the first time, let me help you.

Well, every server that has SQL Server installed has a command line tool installed called sqlcmd.exe. Hey, if I give you a command line you can run it, even if you don’t know what is really going on.

So just open a command prompt and run this command:

C:\Users\UserName> sqlcmd -E -i c:\path\to\instnwnd.sql

Changed database context to ‘master’.
Changed database context to ‘Northwind’.

Ok, the database installed now what?
Well, now you have the Northwind database installed.

From here you are one your own getting whatever sample code you have to connect to this database and compile.

November 3, 2009

How to execute SQL statement that has a single quote in C# or insert a row with a value that has a quote?

Filed under: C# (C-Sharp),MS SQL Server — J. Abram barneck @ 4:20 am

Imagine you have a query such as the following:

SELECT * FROM User WHERE LastName='O'Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo'conner','joconner@somedomain.tld')

Well, that is obviously not going to work, because the apostrophe or single quote in the name O’Conner is going to break the query syntax.

You have to have two single quotes to use a quote.

SELECT * FROM User WHERE LastName='O''Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo''conner','joconner@somedomain.tld')

Ok, so there are two ways to make sure you have two quotes in C#:

  1. You manage the query string yourself.
  2. You use a DataTable and let it manage the query string for you.

Managing the query string yourself

Ok, the answer is simple. You need two single quotes next to each other.

Now, when you have single string, this is easy to do. You need to replace each instance of a single quote with two single quotes using this function which already exists for you:

string.replace(string inStringToBeReplaced, string inNewString)

Here is an example of doing it wrong, then fixing it. Step through this in a debugger.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email     = "joconner@domain.tld";

            // Both these queries are broken because of the space.
            string strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            string strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" + 
                        "'" + FirstName + "'," + 
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";
            
            // This will actually break your query too, because it will replace valid single quotes
            // with two single quotes.  You need to do this on the actually data strings.
            strQuery1 = strQuery1.Replace("'", "''"); //
            strQuery2 = strQuery1.Replace("'", "''");

            // Replace any intance of a single quote with two single quotes, ''.
            // IMPORTANT: Typing two single quotes ('') is not the same as a double quote (").
            FirstName = FirstName.Replace("'", "''");
            LastName = LastName.Replace("'", "''");
            UserName = UserName.Replace("'", "''");
            Email = Email.Replace("'", "''");

            // Both these queries are working now;
            strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" +
                        "'" + FirstName + "'," +
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";         
        }
    }
}

Using a DataTable to manage this for you automagically

This actually looks like more work at first, but really when handling a lot of data, it is much more easy to code using DataTables and DataRows.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName  = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email        = "joconner@domain.tld";

            // Create the connection
            string mConnectionString = "Data Source=ServerName; user id=UserName; password=pw; Initial Catalog=DatabaseName;";
            SqlConnection mSqlConnection = new SqlConnection(mConnectionString);
            
            // Create a data adapter, this is what does the magic.
            String mQueryForSqlDataAdapter = "Select * from TableName";
            SqlDataAdapter tmpSqlDataAdapter;
            SqlCommandBuilder tmpSqlCommandBuilder;
            DataTable tmpDataTable = new DataTable();
            tmpSqlDataAdapter = new SqlDataAdapter(mQueryForSqlDataAdapter, mSqlConnection);

            // Use the SqlDataAdapter to create a table with the right schema but no data
            tmpDataTable = tmpSqlDataAdapter.FillSchema(tmpDataTable, SchemaType.Mapped);

            // Create a SqlCommandBuilder
            tmpSqlCommandBuilder = new SqlCommandBuilder(tmpSqlDataAdapter);

            // Create a DataRow and populate it
            DataRow row = tmpDataTable.NewRow();
            row["FirstName"] = FirstName;
            row["LastName"] = LastName;
            row["UserName"] = UserName;
            row["Email"] = Email;

            // Add this row to the DataTable
            tmpDataTable.Rows.Add(row);

            // Write this to the database
            tmpSqlDataAdapter.Update(tmpDataTable);   
        }
    }
}

Notice we didn’t have to do a string replace of ‘ for ”.

October 30, 2009

How to restart the AUTOINCREMENT number for a table in Microsoft SQL 2008?

Filed under: MS SQL Server — J. Abram barneck @ 2:37 pm

Ok, so I am in the middle of developing a database tool and so I populate a bunch of data, (by adding a bunch of rows), to a column that is AUTOINCREMENT.

So I have 3725 rows for a feature that is working. Now I am developing other features and debuggin them and so I want to reset the database to the same point it was before I started debugging.

So I am deleting all the rows above 3725, however my next AUTOINCREMENT number continues to go up.

I have this handy little SQL statement that should fix that:

To set the table back to 0.

DBCC CHECKIDENT (MyTable, RESEED, 0)

Now, if I set the table back to 0 that can be a problem if you still have rows in it.

The next row you try to insert will give you an error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Person’. Cannot insert duplicate key in object ‘dbo.Person’.
The statement has been terminated.

So if you have rows, try this:

DECLARE @size int
SET @size=(SELECT COUNT(*) From MyTable)
DBCC CHECKIDENT (MyTable, RESEED, @size)

Happy day, it works!!

October 15, 2009

Equivalent of mysqldump for Microsoft SQL Server 2008

Filed under: MS SQL Server — J. Abram barneck @ 6:21 am

There is a Database Publishing Wizard 1.1 you can download that may work for SQL Server 2005, but didn’t work for me with SQL Server 2008. However,Database Publishing Wizard 1.3 is installed with Visual Studio 2008 but I cannot find a separate download. This tool gets you the schema and data and everything but the “drop and create database” script.

So I think you need Visual Studio 2008 for this for SQL Server 2008 to get it. I am not sure why I cannot find it separately. Maybe Microsoft has a reason.

Step 1 – In Visual Studio 2008, go to Tools | Connect to Database and connect to a MS SQL database.

Under the Server Explorer window, the connection now appears.

Step 2 – Expand Data Connections.

Step 3 – Right-click on the connection and choose Publish to provider.

Step 4 – Click Next.

Step 5 – Choose the database.

Step 6 – Click Next.

Step 7 – Select the publishing options (such as to export the schema and data or just the schema).

Step 8 – Choose a file.

Step 9 – Click Finish.

Step 10 – The one thing this is missing is the script to drop and create the database. You can easily get this from Microsoft SQL Server Management Studio 2008 (there is a free Express version if you don’t have it). Just connect to the database, right-click on the database and choose Script Database as | Drop And Create to | Clipboard. Now past this text to the top of your file you just created.

September 25, 2009

What is the Microsoft SQL equivalent to MySQL's "Limit" feature in a SQL query?

Filed under: MS SQL Server,MySQL — J. Abram barneck @ 4:49 pm

Here is a MySQL Query

SELECT * FROM Table LIMIT 10

Here is a Microsoft SQL Query to perform the same

SELECT TOP10 * FROM Table

What is the Microsoft SQL equivalent to MySQL’s “Limit” feature in a SQL query?

Filed under: MS SQL Server,MySQL — J. Abram barneck @ 4:49 pm

Here is a MySQL Query

SELECT * FROM Table LIMIT 10

Here is a Microsoft SQL Query to perform the same

SELECT TOP10 * FROM Table

September 24, 2009

How to check if a SQL Table exists in C#?

Filed under: C# (C-Sharp),MS SQL Server — J. Abram barneck @ 5:38 pm

Simple question, simple answer

SQL Query to return the data

SELECT TABLE_NAME FROM DBName.INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='Article'

How do I check this in C#?

As for how you check that in C#? You just make sure the Row count returned from the query is 1. See this article I have already posted.

How do I get the number of rows returned from a Microsoft SQL Query in C#?

September 22, 2009

How to insert a row into a Microsoft SQL database using C#?

Filed under: C# (C-Sharp),MS SQL Server — J. Abram barneck @ 11:10 pm

The following example accomplishes this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace InsertToDatabase
{
	public class InsertToDatabase
	{
		string connectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
		string query = "INSERT INTO Users (Firstname, Lastname, Email) VALUES ('Jared','Barneck','Jared.Barneck@somedomain.tld')";
		SqlConnection connection = new SqlConnection(connectionString);
		SqlCommand command = new SqlCommand(query, connection);
		connection.Open();
		command.ExecuteNonQuery();
		connection.Close();
	}
}

What is the difference between a DataSet and a DataTable?

Filed under: C# (C-Sharp),MS SQL Server,MySQL — J. Abram barneck @ 5:09 pm

What is the difference between a DataSet and a DataTable?
Here is a link to the MSDN class information for both:
DataSet Class
DataTable Class

So a DataTable is just a table.

However, a DataSet is a collection of tables that can have their data linked together with a DataRelation Class.

So when accessing a database, which should you use?
Well, if you only need a single table, then just use a DataTable.
However, if you need multiple tables and those tables may have some type of relationship, use a DataSet.

Next Page »

Create a free website or blog at WordPress.com.