Tag: ado.net

SQL Bulk copy versus Structured Table Insert

Just wondering if there are any performance differences between structured table inserts and bulk copy. Any experience or ideas which might be performant before I start implementing it. I am using Spring.net for the database layer calls. Sql Bulk Insert using (SqlBulkCopy bulkCopy = new SqlBulkCopy(this.connectionstring) { bulkCopy.DestinationTableName = “BulkDataTable”; bulkCopy.WriteToServer(dataTable); } Sql Structured type […]

ADO.NET what to use to set multiple output parameters – ExecuteScalar, ExecuteReader or ExecuteNonQuery or nothing?

I have following working code. This has 1 input and two output parameters. Which ado.net method should I use? OneInputTwoOutput oneInputTwoOutput = new OneInputTwoOutput(); var Param = new DynamicParameters(); Param.Add(“@Input1”, Input1); Param.Add(“@Output1”, dbType: DbType.Boolean, direction: ParameterDirection.Output); Param.Add(“@Output2”, dbType: DbType.Boolean, direction: ParameterDirection.Output); try { using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[“connectionString”])) { using (SqlCommand cmd = new […]

Unable to insert to SQL Server database

using (Connection = new SqlConnection(connectionstring)) { SqlCommand voegtoe = new SqlCommand(“INSERT INTO Speler(Naam, Rugnummer, Leeftijd) VALUES(@Naam, @Rugnummer, @Leeftijd)”); voegtoe.CommandType = CommandType.Text; voegtoe.Connection = Connection; voegtoe.Parameters.AddWithValue(“@Naam”, textBox1.Text); voegtoe.Parameters.AddWithValue(“@Rugnummer”, textBox2.Text); voegtoe.Parameters.AddWithValue(“@Leeftijd”, textBox3.Text); Connection.Open(); voegtoe.ExecuteNonQuery(); } If I open my Database there’s nothing added to it. I think it should add the text what the user puts in […]

ADO.NET – SQL bit value “1” still being read as “false” by DataReader

If I run my stored proc from SSMS, I can clearly see a 1 in the column in question (which should return true in ADO), but when in my midtier, the DataReader always returns false. I feared this may be because I am casting the value to a bit in my SELECT statement, but if […]

How to get number of rows updated by a query in .net

In my database, i have a trigger which insert the change log entries when a row in Table tblA is updated. Now, in my code i have to update it through a plain Sql query like int count = DBContext.ExecuteStoreCommand(“<sql query to update records>”); This count variable contains the number of rows affected(no of rows […]

XML assigned to SqlXml parameter got extra-encoded?

The following code passes an XML parameter to a stored procedure. The SqlXml is converted from a string. var range = GetXml(“<Range><Column Name=\”Id\” Low=\”5002\” High=\”8076\” /></Range>”); cmd.Parameters.Add(new SqlParameter(“@Range”, SqlDbType.Xml) { Value = range }); SqlXml GetXml(string s) { var memoryStream = new MemoryStream(); var settings = new XmlWriterSettings(); settings.OmitXmlDeclaration = true; settings.ConformanceLevel = ConformanceLevel.Fragment; using […]

Data source controls and parameter type conversion

Q1 – In the code example below runtime converts a value between two incompatible types: <SelectParameters> <asp:Parameter Name=”City” Type=”Int32″ /> </SelectParameters> protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {e.Command.Parameters[“@City”].Value = “100”;} Exception I got: “Conversion failed when converting the nvarchar value ‘Seattle’ to data type int.” A) The above exception suggests that runtime did manage to […]

Number of total select statement for particular web page?

I was wondering what is the easiest way to see total number of database queries from my ASP.Net (.NET 2.0) application. My application heavily use sql 2005 database because all data are dynamic and everything goes through one connection string in web.config. Connection pooling is enabled there. So, I am wondering how many select statements […]

How do reduce transaction log growth for batched nvarchar(max) updates

Our app needs to add large amounts of text to SQL Server 2005 database (up to 1 GB for a single record). For performance reasons, this is done in chunks, by making a stored procedure call for each chunk (say, usp_AddChunk). usp_AddChunk does not have any explicit transactions. What I’m seeing is that reducing the […]

How do I avoid these deadlocks?

I have a routine which is updating my business entity. The update involves about 6 different tables. All the commands are being executed within a transaction. Recently, I needed to add some code into the routine which accesses a lookup table from the database. The lookup code already existed in another business object so I […]

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