|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Direct Data Access - Best Practice?like disconnected for some reason. What I'm wondering is if the method I use is acceptable or if there is a better way. This is the basic code I use every time I want to run a query (of course there are modifications depending on the type of query): Dim connStr As String = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ConnectionString Dim conn As New System.Data.SqlClient.SqlConnection(connStr) Dim cmd As New System.Data.SqlClient.SqlCommand("DELETE * FROM [customer]", conn) Using conn conn.Open() cmd.ExecuteNonQuery() End Using I use this code a whole bunch, so I'm just wandering if this is alright or if there is a better way. Thanks, Matt The only thing I see that I would change is to use "TRUNCATE TABLE
[customer]" instead of DELETE. This approach is fine. There is no reason to use a disconnected DataTable for this unless you want the program to run dramatically slower... ;) -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Saubz" <saubz@newsgroups.nospam> wrote in message news:30192AB6-B6A9-4F80-B3B4-D3419E6C5F91@microsoft.com... > Hello. I currently use a lot of direct data access in ASP - I really > don't > like disconnected for some reason. What I'm wondering is if the method I > use > is acceptable or if there is a better way. This is the basic code I use > every time I want to run a query (of course there are modifications > depending > on the type of query): > > Dim connStr As String = > ConfigurationManager.ConnectionStrings("CustomerConnectionString").ConnectionString > Dim conn As New System.Data.SqlClient.SqlConnection(connStr) > Dim cmd As New System.Data.SqlClient.SqlCommand("DELETE * FROM > [customer]", > conn) > Using conn > conn.Open() > cmd.ExecuteNonQuery() > End Using > > I use this code a whole bunch, so I'm just wandering if this is alright or > if there is a better way. > > Thanks, > Matt Bill,
Thanks for your response. My question was more about the structure of how I'm declairing and using my connection to my database - not about the type of sql statement. I use the essentially the same code for all my select, insert, update, and delete statements. I end up with that little paragraph of code all over my applications. I was just wondering if the approach I was using to declaire my connection, use my connection, execute my sql, and terminate my connection is in-line with best practices. Thanks, Matt Show quote "William (Bill) Vaughn" wrote: > The only thing I see that I would change is to use "TRUNCATE TABLE > [customer]" instead of DELETE. > > This approach is fine. There is no reason to use a disconnected DataTable > for this unless you want the program to run dramatically slower... ;) > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "Saubz" <saubz@newsgroups.nospam> wrote in message > news:30192AB6-B6A9-4F80-B3B4-D3419E6C5F91@microsoft.com... > > Hello. I currently use a lot of direct data access in ASP - I really > > don't > > like disconnected for some reason. What I'm wondering is if the method I > > use > > is acceptable or if there is a better way. This is the basic code I use > > every time I want to run a query (of course there are modifications > > depending > > on the type of query): > > > > Dim connStr As String = > > ConfigurationManager.ConnectionStrings("CustomerConnectionString").ConnectionString > > Dim conn As New System.Data.SqlClient.SqlConnection(connStr) > > Dim cmd As New System.Data.SqlClient.SqlCommand("DELETE * FROM > > [customer]", > > conn) > > Using conn > > conn.Open() > > cmd.ExecuteNonQuery() > > End Using > > > > I use this code a whole bunch, so I'm just wandering if this is alright or > > if there is a better way. > > > > Thanks, > > Matt > > > In an ASP application, that's precisely what you need to do.
AFA having the same code everywhere, I've seen applications that try to move all of the action commands to a single function that accepts the SQL and does all of the work in one place. This won't work as easily for rowset-returning queries. If the SQL is more sophisticated, I suggest using SPs. Again, anytime you hard-code the table or column names you'll find that the code gets more brittle--when the schema changes you're pooched... -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Saubz" <saubz@newsgroups.nospam> wrote in message news:A7E1D3C3-38AC-4C17-BC9B-810DBBA6168A@microsoft.com... > Bill, > Thanks for your response. > > My question was more about the structure of how I'm declairing and using > my > connection to my database - not about the type of sql statement. I use > the > essentially the same code for all my select, insert, update, and delete > statements. I end up with that little paragraph of code all over my > applications. > > I was just wondering if the approach I was using to declaire my > connection, > use my connection, execute my sql, and terminate my connection is in-line > with best practices. > > Thanks, > Matt > > > > "William (Bill) Vaughn" wrote: > >> The only thing I see that I would change is to use "TRUNCATE TABLE >> [customer]" instead of DELETE. >> >> This approach is fine. There is no reason to use a disconnected DataTable >> for this unless you want the program to run dramatically slower... ;) >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Saubz" <saubz@newsgroups.nospam> wrote in message >> news:30192AB6-B6A9-4F80-B3B4-D3419E6C5F91@microsoft.com... >> > Hello. I currently use a lot of direct data access in ASP - I really >> > don't >> > like disconnected for some reason. What I'm wondering is if the method >> > I >> > use >> > is acceptable or if there is a better way. This is the basic code I >> > use >> > every time I want to run a query (of course there are modifications >> > depending >> > on the type of query): >> > >> > Dim connStr As String = >> > ConfigurationManager.ConnectionStrings("CustomerConnectionString").ConnectionString >> > Dim conn As New System.Data.SqlClient.SqlConnection(connStr) >> > Dim cmd As New System.Data.SqlClient.SqlCommand("DELETE * FROM >> > [customer]", >> > conn) >> > Using conn >> > conn.Open() >> > cmd.ExecuteNonQuery() >> > End Using >> > >> > I use this code a whole bunch, so I'm just wandering if this is alright >> > or >> > if there is a better way. >> > >> > Thanks, >> > Matt >> >> >> Hi Matt,
First of all, I'd like to confirm my understanding of this issue. According to your description, you want to know what is the best way to access the database by ADO.net. If I misunderstand anything here, please don't hesitate to correct me. In your special case, as Bill said, we would like to suggest you use Store Procedure and close the connection each time the command has been executed. Also please don't disable the connect pool in your ConnectionString. As below: Dim connStr As String = ConfigurationManager.ConnectionStrings("CustomerConnectionString").Connectio nString() Dim conn As New System.Data.SqlClient.SqlConnection(connStr) ************************ Dim cmd As New System.Data.SqlClient.SqlCommand() cmd.Connection = conn cmd.CommandText = "SP_NAME" cmd.CommandType = CommandType.StoredProcedure ************************* Using conn conn.Open() cmd.ExecuteNonQuery() ************************* conn.Close() ************************* End Using Also you can get detailed information from the following documents http://msdn2.microsoft.com/en-us/library/ms998569.aspx [Improving ADO.NET Performance] http://msdn2.microsoft.com/en-us/library/ms971481.aspx [Best Practices for Using ADO.NET] If anything is unclear, please feel free to post in the newsgroup and we will follow up. I'm glad to work with you. Have a good day! Best regards, Wen Yuan  |
|||||||||||||||||||||||
Other interesting topics