Rhyous

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#?

Return to ADO.NET and Database with C#

The following steps accomplishes this:

1. Create a connection string.
2. Create an insert query string.
3. Create a SQLConnection object that uses the connection string.
4. Create a SqlCommand object that used both the SQLConnection and the query string.
5. Open the connection.
6. Execute the query.
7. Close the connection.

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

namespace InsertToDatabase
{
	public class InsertToDatabase
	{
		// Step 1 - Create a connection string.
		string connectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
		// Step 2 - Create an insert query string
		string query = "INSERT INTO Users (Firstname, Lastname, Email) VALUES ('Jared','Barneck','Jared.Barneck@somedomain.tld')";
		// Step 3 - Create a SQLConnection object that uses the connection string.
		SqlConnection connection = new SqlConnection(connectionString);
		// Step 4 - Create a SqlCommand object that used both the SQLConnection and the query string.
		SqlCommand command = new SqlCommand(query, connection);
		// Step 5 - Open the connection.
		connection.Open();
		// Step 6 - Execute the query.
		command.ExecuteNonQuery();
		// Step 7 - Close the connection.
		connection.Close();
	}
}

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.

This was a little bit easier for me because I had just figured all this out on Microsoft SQL and (lo and behold), MySQL had created the exact same function structure for MySQL.

So I read through this document first:
Beginning MYSQL 5 with Visual Studio.NET 2005.pdf

It was very informative and showed this code to get into a MySQL database:

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet, "users");
}

I had been accessing tables in a Microsoft SQL database using a different set of functions, so I tested to see if the method I had been using for Microsoft SQL would work for MySQL, since the object and function names are almost identical.

The following code also accessed the database and I like it better because a DataTable seems more of an obvious choice to return data from table.

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlCommand command = new MySqlCommand(query, connection);
    MySqlDataReader reader = command.ExecuteReader();;
    DataTable table = new DataTable();
    table.Load(reader);
}

So I left wondering how these two methods are different…I guess I need to answer the following:

1. Which of the above two SQL database access methods should be considered best practice and why?
2. Is one more efficient than the other?
3. What is the difference between a DataSet and a DataTable?
   DataSet Class
   DataTable Class

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

Having used other languages where this is much simpler, I was surprised at how “not simple” this was in C#. I expected it to be a little more complex than in some scripting language such as PHP, but it was way more complex.

Here is how I do it:

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

namespace CountRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a string to hold the database connection string
            string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";

            // Create a string to hold the database connection string
            string query = "SELECT * FROM MyTable";

            // Pass both strings to a new SqlCommand object.
            SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection);

            // Create a SqlDataReader
            SqlDataReader queryCommandReader = queryCommand.ExecuteReader();

            // Create a DataTable object to hold all the data returned by the query.
            DataTable dataTable = new DataTable();
            dataTable.Load(queryCommandReader);

            // The DataTable object has a nice DataTable.Rows.Count property that returns the row count.
            int rowCount = rowCount = dataTable.Rows.Count;
        }
    }
}

Now doing it this way, you also have the data available in the DataTable dataTable object so you don’t have to go to the database and get it again.

Return to ADO.NET and Database with C#

How I get the number of rows in a table using C#?

Here is a step by step:

Step 1 – Create a new class: (attached click here: SQLTableRowCounter.cs)

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

namespace SQLTableRowCounter
{
    class SQLTableRowCounter
    {
        private string mCountQuery;
        private SqlConnection mConnection;
        private int mNumberOfRows;

        public SQLTableRowCounter(String inTableName, SqlConnection inConnection)
        {
            mCountQuery = "SELECT COUNT(*) FROM " + inTableName;
            mConnection = inConnection;
            mConnection.Open();
            SqlCommand mCountQueryCommand = new SqlCommand(mCountQuery, mConnection);
            mNumberOfRows = (int)mCountQueryCommand.ExecuteScalar();
        }

        public int NumberOfRows
        {
            get { return mNumberOfRows; }
            set { mNumberOfRows = value; }
        }
    }
}

Step 2 – Now create the object and get the value:

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

namespace SQLTableRowCounter
{
    class Program
    {
        static void Main(string[] args)
        {
             string connectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
             SqlConnection connection = new SqlConnection(connectionString);
             SQLTableRowCounter qrc = new SQLTableRowCounter("TableName", connection);
             int numRows = qrc.NumberOfRows;
        }
    }
}