Thursday, July 30, 2009

What is the best method for inserting data into an Access or SQL database using C# and .NET?

I am a Classic ASP programmer learning .NET and C# (and trying not to pull out my hair).





I'm used to setting recordsets via ADO in C-ASP, should I do the same in C#? I already know that I can add references to the ADO libraries and create recordsets, but I'm not sure if this is the best practice.





If you need more detail, let me know...

What is the best method for inserting data into an Access or SQL database using C# and .NET?
first add





using System.Data;


using System.Data.SqlClient;





import statements at the top of the code page.





Then copy the following functions to your code:





public static SqlConnection GetConnection(string strConn)


{


SqlConnection conn=null;


try


{


conn = new SqlConnection(strConn);


conn.Open();


}


catch (SqlException ex)


{


Console.Write("SQL ERROR: " + ex.Message);


}


catch (Exception ex)


{


Console.Write("ERROR: " + ex.Message);


}


return conn;


}





public int ExecuteNonQuery(string strConn,string query)


{


SqlConnection conn = GetConnection(strConn);


int result=0;


SqlCommand cmd = new SqlCommand(query, conn);





try


{


//Configure the SqlCommand object


cmd.Connection = conn;


cmd.CommandType = CommandType.Text;


//Set type to StoredProcedure





cmd.CommandText = query;


result = cmd.ExecuteNonQuery();





}


catch (Exception ex)


{


Console.Write("Error running a query " + query + ": " + ex.Message);


}


finally


{


conn.Close();


}


return result;


}





And then do the following:





string query="INSERT INTO Table1(Field1,Field2)VALUES("test","test...


string strConn="Your database connection string here";


int output=ExecuteNonQuery(strConn,query);








if output=0 the insert failed, otherwise succeeded.





If you are dealing with access database, use the same functions but replace SqlConnection and SqlCommand with OleDbConnection and OleDbCommand
Reply:Stick with ADO.NET and SQL statements. It is indeed the best way to go. ADO.NET should work pretty much the same as you're used to and SQL statements distribute the work to be done onto the database server.
Reply:Use foxy's method, that is exactly what I was going to post. You can use the ADO libraries, but they are horrendously slow in .Net. When LINQ becomes available for Access, that is going to be the way to go.
Reply:If you're using MsSQL I'd recommend to look at Microsoft's new LINQ query and programming language fusion. It doesn't work for Access yet, but I think the thing is brilliant.


LINQ is available in Visual Studio 2008
Reply:I am with the other poster who suggested sticking with the ADO object library. It is time tested, and supported by all of the products you mention. If you are already familiar with recordsets, you should have no problem implementing them in C#. My motto is always : when in doubt, stick with what you know. Good luck. If you need any help or advice, just let me know. Be glad to assist in any way I can.


No comments:

Post a Comment