connecting to sql database c# asp.net

Question

hallo there

This is a very basic question. I am currently a student and have done ASP.NET with C#. For our purposes it was required to do work with an access database where connecting to it and adding data etc.was very easy.

My feeling is that access is not used much in the real world and would just like to enquire on the easiest and most correct way of establishing a connection to Microsoft Sql Server Database(Transact sql). In my case the database is called dbActivities with primary data file being dbActivitiesData.mdf.

OleDbDataConnection conn;
conn = new OleDbConnection = @"Provider=Microsoft.Jet.Oledb.4.0:"
                           @"Data Source=DataBase.mdb";
conn.Open();

Regards

Darin Dimitrov · Accepted Answer · 2011-06-19 10:21:15Z

My feeling is that access is not used much in the real world

Unfortunately Access is still very much used in the real world :-)

As far as the correct way is concerned I would recommend you wrapping the connection into a using block to ensure proper handling:

class Program
{
    static void Main()
    {
        var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\work\DataBase.mdb";
        using (var conn = new OleDbConnection(connectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = "SELECT Name FROM Customers";
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var customerName = reader.GetString(reader.GetOrdinal("Name"));
                    Console.WriteLine(customerName);
                }
            }
        }
    }
}

And as far as Microsoft SQL Server is concerned:

var connectionString = @"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;";
using (var conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT Name FROM Customers";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var customerName = reader.GetString(reader.GetOrdinal("Name"));
            Console.WriteLine(customerName);
        }
    }
}

kleinohad · Accepted Answer · 2011-06-19 10:21:48Z

1

string strSQLCommand; 
SqlCommand command;
SqlConnection conn = null;
conn =new SqlConnection("Data Source=serverName\IP;Initial Catalog=dbActivities;UID=User;PWD=Password;Max Pool Size=500;");
strSQLCommand = "Your Command";
command = new SqlCommand(strSQLCommand, conn);
command.ExecuteNonQuery();
conn.Close();

answered Jun 19, 2011 at 10:21

kleinohad

5,9202 gold badges29 silver badges35 bronze badges

Collectives™ on Stack Overflow

connecting to sql database c# asp.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related