Archive for the ‘csharp’ Category.

A new style guide or a new way to format your code in C++ or C# or any language: "Code like you speak"

Code Like You Speak

Ok, so I wrote a post about why I use long variable names, where I discussed how it is very easy to use long filenames with today’s IDEs. As I code more and more I realize how I want to write like I speak.

As I try to read other people’s code, I have to wonder if they could even understand it themselves if they went six months without touching it.

For example, lets say I am working with a database that has phone numbers and for each phone number I want to do check if it is valid:

Example 1

PhoneNumber p = new PhoneNumber("555-555-5555");
if (p.isValid())
{
    // do something
} else
{
    // Do something different
}

There is nothing wrong with the above code. It even makes sense, mostly because the first line where p is created is right next to the if statement. But of course that is not always the case.

Maybe I have dozens of objects for different database attributes including multiple ojects that start with ‘p’: PhoneNumber, PartList, PersonID, and they all have an isValid() method and those objects are created elsewhere in the code, who knows where. Also, your isValid() function in PhoneNumber checks if the PhoneNumber is formatted correctly, but the isValid() function in PartList checks if it is a valid part in the database and the isValid() function in PersonID does something different, now the above code would be more confusing, especially to some one else reading it (or to yourself six months after writing it).

if (p.isValid())
{
    // do something
} else
{
    // Do something different
}

So if we don’t have the declaration right next to the code, it is hard to understand…

You might say, “Load it in a debugger and just look…why is it so hard?”

My response is this. “What about your Team Leads for you Tech Support team? What about your documentation team? They may have that has access to read the code but they don’t have the tools or know how to compile it. But they may need to see your code to get their job done. And I don’t want to offend your project managers, but there sure are a lot of Project Managers that either never had the ability or have lost the skill to compile code.”

So someone else reading your code is lost. What does p represent? PhoneNumber, PartList, PersonId? What is the object? What does isValid() check for on this object?

So lets code like we speak. First you should say what we really want to do in your language. The sentence below is a valid English sentence and anybody who speaks English can understand it.

If the current phone number is formatted correctly, do something, else do something different.

Well, what if the code were written like this:

// This could be anywhere in code
PhoneNumber theCurrentPhoneNumber = new PhoneNumber("555-555-5555");

// A code like you speak if statement
if (theCurrentPhoneNumber.isFormattedCorrectly())
{
    // do something
} else
{
    // do something different
}

See how it is in “code like you speak” format?

So you may here different theories about writing code and whether to document with comments or not document and have clear code. I am for the theory that when you write your code you should assume that the person reading your code:
1. Doesn’t understand code.
2. Has no technical skills
3. Has an average IQ (in the 90s).
4. Is the worst technical ever who has to document your code…
etc, etc, etc…

So that is why “Code like you speak” is a great way to code. If your technical writer reads the first example, he is confused, but if he read the second, he fully understands the idea what it going one.

Now, it is important that you don’t over do it. You don’t need to have perfect English. Broken English is fine. As you practice you will get better at it. As you get better at it, your code becomes more readable and others can work on it easier. This is especially usefully when working in teams, or one a community-developed open source application.

Don’t go overboard! I mean, there is not reason to define the word “othewise” to replace “else”. If you did that, you may actually confuse veteran developers.

// In a header somewhere...
#define otherwise else

// This could be anywhere in code
PhoneNumber theCurrentPhoneNumber = new PhoneNumber("555-555-5555");

// A code like you speak if statement
if (theCurrentPhoneNumber.isFormattedCorrectly())
{
    // do something
}
otherwise
{
    // do something different
}

However, if the “Code like you speak” development style takes off, the above may not be so overboard, and may become a standard practice to define many English synonyms to help one code in a more clear and understandable method.

One might argue that because not every one speaks english, a language barrier would be more likely, and I would have to disagree. If you code like you speak, someone who doesn’t speak your language could still figure it out a lot faster using something like a language translation tool (such as google’s or babblefish’s) than they could by trying to figure out what a random object is.

As for other style guide information, there are lots of style guides. If you need one and don’t have one, start with this one:
http://google-styleguide.googlecode.com/svn/trunk/cppguide.xml

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

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 ”.

How to store a password in an XML file encrypted so it is not in clear text or how to encrypt any textstring?

Ok, so I have an application that needs to take a password and I need to remember that password. All the configuration is stored in XML, which is usually clear text. I want to store the password in the XML file, but I don’t want anyone to be able to open the XML file and be able to see the password in clear text.

So here is what I going to do.

I create a class called PasswordEncoder that is going to use DESCryptoServiceProvider, which is a C# function.

I found a few examples online that helped me create this, such as Microsoft’s site that explains this object and another users blog that shows an example, which I used but only slightly modified.
http://msdn.microsoft.com/en-us/library/system.security.cryptography.descryptoserviceprovider.aspx
http://www.dotnetspider.com/resources/21370-Password-Encryption-using-C.aspx

So here is my source. All you have to do is create your own class and copy in this code and you are ready to encrypt and decrypt passwords you store in XML.

Important! You must change the mInitializationVector and the mByteArray variable values to be your own values. Yes you can simply make up your own values.  We don’t want a everyone using the same keys.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

namespace PasswordEncoder
{
    class PasswordEncoder
    {
        string mEncryptedPassword;
        // Change the two values below to be something other than the example.
        // Once changed and in use, do not change the value below again or you
        // won't be able to decrypt previously stored passwords.
        string mByteArray = "%$#>#%232s+as#l)URa0$!@";
        byte[] mInitializationVector = { 0x01, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xf7, 0xEF };

        public PasswordEncoder()
        {
        }

        public PasswordEncoder(string inPassword)
        {
            mEncryptedPassword = EncryptWithByteArray(inPassword, mByteArray);
        }

        public string EncryptWithByteArray(string inPassword)
        {
            mEncryptedPassword = EncryptWithByteArray(inPassword, mByteArray);
            return mEncryptedPassword;
        }

        private string EncryptWithByteArray(string inPassword, string inByteArray)
        {
            try
            {
                byte[] tmpKey = new byte[20];
                tmpKey = System.Text.Encoding.UTF8.GetBytes(inByteArray.Substring(0, 8));
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                byte[] inputArray = System.Text.Encoding.UTF8.GetBytes(inPassword);
                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(tmpKey, mInitializationVector), CryptoStreamMode.Write);
                cs.Write(inputArray, 0, inputArray.Length);
                cs.FlushFinalBlock();
                return Convert.ToBase64String(ms.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string DecryptWithByteArray()
        {
            return DecryptWithByteArray(mEncryptedPassword, mByteArray);
        }

        private string DecryptWithByteArray(string strText, string strEncrypt)
        {
           try
           {
                byte[] tmpKey = new byte[20];
                tmpKey = System.Text.Encoding.UTF8.GetBytes(strEncrypt.Substring(0, 8));
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                Byte[] inputByteArray = inputByteArray = Convert.FromBase64String(strText);
                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(tmpKey, mInitializationVector), CryptoStreamMode.Write);
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
                System.Text.Encoding encoding = System.Text.Encoding.UTF8;
                return encoding.GetString(ms.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string EncryptedPassword
        {
            get { return mEncryptedPassword; }
            set { mEncryptedPassword = value; }
        }

        public string ByteArray
        {
            get { return mByteArray; }
            set { mByteArray = value; }
        }
    }
}

Here is a simple sample of how to use this:

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

namespace PasswordEncoder
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("Enter a password: ");
            string password = Console.ReadLine();
            Console.WriteLine("You entered this password: " + password);

            PasswordEncoder pe = new PasswordEncoder();
            string encryptedPassword = pe.EncryptWithByteArray(password);
            Console.WriteLine("Your encrypted password string: " + encryptedPassword);

            string decryptedPassword = pe.DecryptWithByteArray();
            Console.WriteLine("Your decrypted password string: " + decryptedPassword);

            if (password.Equals(decryptedPassword))
            {
                Console.WriteLine("Good work, your password was successfully encrypted then decrypted.");
            }
            else
            {
                Console.WriteLine("Uh...what did you do wrong, these don't match.");
            }
        }
    }
}

Where to get free training and tutorial videos for WPF?

So WPF doesn’t seem very well documented. I have been struggling to find good documentation and learn all its intricacies. I like tutorials and training videos and examples and samples, all of which have been difficult to come by.

So I thought I would share with everyone the following web site where Microsoft provides quite a lot of training videos on WPF.

WPF Videos
http://windowsclient.net/learn/videos_wpf.aspx

If you take time to watch all these videos, you will probably be a master at WPF very quickly.

A good site for learning WPF ListView as GridView development in C# and WPF

So i needed to make a table layout in WPF and I noticed that it is not that simple to learn.

However, I found a really good site that teaches you how to populate a ListView using a GridView from XML, a DataTable, data from a database, etc…

The new WPF GridView customized 1 of 3
http://www.codeproject.com/KB/miscctrl/GridView_WPF.aspx

I spent all day trying to figure out what this site taught me in less than an hour.

How to remove an element from a regular array in C#?

How to remove an element from an regular array in C#?

Well, you can’t really change a regular array or remove an item from it. You have to create a new array that is a copy of the current array without the one value you want.

Here is a quick static class I created that will copy your array, leaving out the one item you don’t want.

namespace ArrayItemDeleter
{
static class ArrayItemDeleter
{
public static void RemoteArrayItem(ref T[] inArray, int inIndex)
{
T[] newArray = new T[inArray.Length – 1];
for (int i = 0, j = 0; i < newArray.Length; i++, j++) { if (i == inIndex) { j++; } newArray[i] = inArray[j]; } inArray = newArray; } } } [/sourcecode] Here is how you would use it in your program. [sourcecode language="csharp"] using System; namespace ArrayItemDeleter { class Program { static void Main(string[] args) { string[] str = {"1","2","3","4","5"}; ArrayItemDeleter.RemoteArrayItem(ref str, 2); } } } [/sourcecode] Now, if you really want to add and remove items a lot, you should use a System.Collections.Generic.List object;

How to connect to Salesforce / SForce with C#?

Step 1 – Download and import the wsdl (sorry no steps for this here yet).

Step 2 – Use the following code example

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

namespace SforceConnection
{
    public class Program
    {
        static void Main(string[] args)
        {
            SforceService service = new SforceService();
            LoginResult loginResult = new LoginResult();
            string username= "youruser@yourdomain.tld";
            string password= "P@sswd!";
            service.Timeout = 60000;
            loginResult  = service.login(username, password);
            service.Url = loginResult.serverUrl;
            service.SessionHeaderValue = new SessionHeader();
            service.SessionHeaderValue.sessionId = loginResult.sessionId;

            // Do something you are now connected

        }
    }
}

How to return a string array from an enum in C#?

So I have a enum and I want to pass it to a ComboBox, so I need it to be an array of strings.

Well, this was much more simple than I thought:

public static string[] EnumToStringArray(Type inType)
{
	return Enum.GetNames(typeof(inType));
}

I found a few sites that were taking some much more complex routes, maybe they didn’t know about this feature.

Working with DateTime and Unix Time Stamps or Universal time (UTC) in C#

I ended up creating an extender class. Here is an example.

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

namespace LANDesk.Install.Common.Extenders
{
    public static class DateTimeExtender
    {
        #region Functions
        /// <summary>
        /// Methods to convert DateTime to Unix time stamp
        /// </summary>
        /// <param name="_UnixTimeStamp">Unix time stamp to convert</param>
        /// <returns>Return Unix time stamp as long type</returns>
        public static long ToUnixTimestamp(this DateTime thisDateTime)
        {
            TimeSpan _UnixTimeSpan = (thisDateTime - new DateTime(1970, 1, 1, 0, 0, 0));
            return (long)_UnixTimeSpan.TotalSeconds;
        }

        #endregion
    }
}

Here is some code you can use for learning, to know what functions do what with DateTime. You should be able to learn everything you need to know by evaluating it.

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

namespace UTC
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime dt = DateTime.Now;

            Console.WriteLine("Outputting the type of DateTime object used:");

            Console.WriteLine(dt.Kind);

            Console.WriteLine("\nOutputting the current time as is:");

            Console.WriteLine(dt);

            Console.WriteLine("\nOutputting the current time using the ToUniversalTime() function:");

            Console.WriteLine(dt.ToUniversalTime());

            Console.WriteLine("\nOutputting the current time using the ToFileTime() function:");

            Console.WriteLine(dt.ToFileTime());

            Console.WriteLine("\nOutputting the current time using the ToFileTimeUtc() function:");

            Console.WriteLine(dt.ToFileTimeUtc());

            Console.WriteLine("\nOutputting the current time using the Ticks property:");

            Console.WriteLine(dt.ToFileTimeUtc());

            Console.WriteLine("\nCreating a DateTime and setting it as follows:\nDateTime udt1 = DateTime.Parse(\"1/1/1970\");");

            DateTime udt1 = DateTime.Parse("1/1/1970");

            Console.WriteLine(udt1);

            Console.WriteLine("\nCreating a DateTime and setting it as follows:\nnew DateTime(1970, 1, 1, 0, 0, 0, 0);");

            DateTime udt2 = new DateTime(1970, 1, 1, 0, 0, 0, 0);

            Console.WriteLine(udt1);

            Console.WriteLine("\nOutputting the time stamp of this Unix Time Stamp value: 1254322574 (seconds)\nTo do this i use these lines:\n DateTime udt1 = new DateTime(1970, 1, 1, 0, 0, 0, 0);\n udt.AddSeconds(1254322574448);");

            Console.WriteLine(udt1.AddSeconds(1254322574));

            Console.WriteLine("\nOutputting the time stamp of this Unix Time Stamp value: 1254322574789 (milliseconds)\nTo do this i use these lines:\n DateTime udt1 = new DateTime(1970, 1, 1, 0, 0, 0, 0);\n udt.AddMilliseconds(1254322574448);");

            Console.WriteLine(udt1.AddMilliseconds(1254322574789));

        }
    }
}

Ok, so here is the output:

Outputting the type of DateTime object used:
Local

Outputting the current time as is:
9/30/2009 3:49:18 PM

Outputting the current time using the ToUniversalTime() function:
9/30/2009 9:49:18 PM

Outputting the current time using the ToFileTime() function:
128988209584600486

Outputting the current time using the ToFileTimeUtc() function:
128988209584600486

Outputting the current time using the Ticks property:
128988209584600486

Creating a DateTime and setting it as follows:
DateTime udt1 = DateTime.Parse(“1/1/1970”);
1/1/1970 12:00:00 AM

Creating a DateTime and setting it as follows:
new DateTime(1970, 1, 1, 0, 0, 0, 0);
1/1/1970 12:00:00 AM

Outputting the time stamp of this Unix Time Stamp value: 1254322574 (seconds)
To do this i use these lines:
DateTime udt1 = new DateTime(1970, 1, 1, 0, 0, 0, 0);
udt.AddSeconds(1254322574448);
9/30/2009 2:56:14 PM

Outputting the time stamp of this Unix Time Stamp value: 1254322574789 (milliseconds)
To do this i use these lines:
DateTime udt1 = new DateTime(1970, 1, 1, 0, 0, 0, 0);
udt.AddMilliseconds(1254322574448);
9/30/2009 2:56:14 PM
Press any key to continue . . .

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

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#

How to pause a Console application in C# or the equivelent to the C++ system("pause") statement?

How to pause a Console application in C# or the equivelent to the C++ system(“pause”) statement?
I actually had to spend way more time to figure this out. I assumed that there was a simple pause function, but there is not. C# has left out that function. So if you are looking for a way to do this with a single statement, you are not going to find it. I searched, and searched, and searched.

I just wanted to pause, something that is common in C++. In C++ people always do the following:

system("pause");

Note: This is not actually an efficient method to do this in C++. It is loading a cmd.exe process and running the pause command inside it. So should you really be loading a separate external process and all its resources every time you want to pause? Well, it isn’t really that many resources so who really cares, right? You could do the same thing in C# by launching a cmd.exe process but it is probably not what you really want to do.

The best solutions I found are documented, one uses a function, one uses a Pause class.

How to pause a Console application in C#?

Step 1 – Create a function in your Program.cs class called pause as shown here:

public static void Pause()
{
	Console.Write("Press any key to continue . . . ");
	Console.ReadKey(true);
}

Step 2 – Call this function in your code. The following is a complete example

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

namespace Pause
{
    class Program
    {
        static void Main(string[] args)
        {
            Pause();
        }

        public static void Pause()
        {
            Console.Write("Press any key to continue . . .");
            Console.ReadKey(true);
        }
    }
}

How to do this as a Pause class?

Step 1 – Create the following class

Pause.cs

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

namespace Pause
{
    class Pause
    {
        public Pause()
        {
            Console.Write("Press any key to continue . . .");
            Console.ReadKey(true);
        }
    }
}

Step 2 – Add this to your project and then call new Pause(); and you did it. As

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

namespace Pause
{
    class Program
    {
        static void Main(string[] args)
        {
            new Pause();
        }
    }
}

How to insert a row into a Microsoft SQL database using 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.
Steps are in the code with comments.
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?

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.

How do I access a MySQL database with C#?

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 Microsoft SQL Query in C#?

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#