Tag: ado.net

Batch Update Table Across Non Linked SQL Servers

I am trying to update a table across “non-linked” SQL servers using: C# or VB.net and ADO.net SqlDataAdapter. I need to use DataTable and SqlDataAdapter. Very Important: I need to use BatchUpdate and avoid looping through the DataTable. The table designs in Server 1 differ from table design in Server 2. Source Table: Server 1. […]

How can I create two commands in one connection?

For example I want to INSERT data in database and also UPDATE another table. My code is like this SqlConnection con = new SqlConnection(“**”); con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = “INSERT Borrowbook VALUES (@StudentID, @ISBN, @Title, @Date)”; SqlParameter p1 = new SqlParameter(“@StudentID”, SqlDbType.NChar); p1.Value = textBox2.Text; cmd.Parameters.Add(p1); SqlParameter p2 = new SqlParameter(“@ISBN”, SqlDbType.NVarChar); p2.Value […]

TSQL try catch transaction error handling, transaction count mismatch

I have a sproc that I am calling from C# with a transaction by doing: using (var dbContext = PowerToolsDatabase.GetDataContext()) { dbContext.Connection.Open(); using (dbContext.Transaction = dbContext.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable)) { foreach (var element in package.AddOrUpdateElements) { dbContext.usp_Element_Commit( /* args */); } dbContext.Transaction.Commit(); } } And in that sproc there is a try catch, and a RAISERROR inside the […]

Copy data from one database to another using VB.NET

I need to copy data from one database to another using a VB.NET program. The target database is SQL Server the source database is some proprietary ODBC compliant database. I need to loop through a list of table to copy. Read the data from the source database table for a given modified date. Delete the […]

How to display query results from SQL server in VB?

I am trying to display query results from SQL server in VB. I wrote following code, but not getting how to “Just display the results”; Public Function ConnectToSQL() As String Dim con As New SqlConnection Try con.ConnectionString = “Data Source=(local);Initial Catalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=afm” Dim cmd As New SqlCommand(“SELECT username FROM users WHERE username=’user'”, con) […]

SQL Parameter for ALL

I was wondering if there was a parameter in SQL for all (not *!) For example, I’m writing a search table now, and if the user does not input something in the text box, it would mean to ignore that specific parameter and display ALL of them for that field. I understand you could make […]

Long time to load first sql connection in .NET

For some reason it takes 7 seconds to open a connection to a sql server database for the firt time, subsequent connections takes a second. any idea what could be the reason? I’m using C# and asp.net Its after compilation, i essence every time i restart the site, which means every time it needs to […]

Passing DataTable to stored procedure as an argument

I have a data table created in C#. DataTable dt = new DataTable(); dt.Columns.Add(“Name”, typeof(string)); dt.Columns.Add(“Age”, typeof(int)); dt.Rows.Add(“James”, 23); dt.Rows.Add(“Smith”, 40); dt.Rows.Add(“Paul”, 20); I want to pass this to the following stored procedure. CREATE PROCEDURE SomeName(@data DATATABLE) AS BEGIN INSERT INTO SOMETABLE(Column2,Column3) VALUES(……); END My question is : How do we insert those 3 tuples […]

What gets disposed when SqlCommand.Dispose is called?

In theory since SqlCommand implements IDisposable a SqlCommand object should always be disposed. I personally wrap a using statement around them. However I see lots of code that never disposes of SqlCommand objects without any apparent problems. I understand that finalizers will ultimately be called by garbage collection but since that can take quite a […]

.NET READPAST lock error when calling a stored procedure

I’m trying to call a stored procedure from my .NET code which has one output paramater. Its all standard ADO.NET stuff using SqlCommand, SqlParameter and so on. But I’m getting the error below even though my transaction level is READ COMMITTED. You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.