Tag: ado.net

Entity Framework mapping of object containing list of same objects

Currently in my code I am doing something like this public class Subject { private List<Subject> _prerequisites; } A subject can have many prerequisites (which are also subjects), and subject can be a prerequisite of many subjects. We were originally using typed datasets to save the data to the database and our tables looked like […]

What isolation level does autocommit transaction use in SQL Server?

When I work with update conflict problem with SNAPSHOT Isolation Level, it seems that autocommit transactions use the isolation level used at last time. Condition: ALLOW_SNAPSHOT_ISOLATION is ON, READ_COMMITTED_SNAPSHOT is OFF Step 1 : Execute update statement without transaction using (var sqlconn = new SqlConnection(“Data source=…”)) using (var sqlcmd = sqlconn.CreateCommand()) { sqlconn.Open(); sqlcmd.CommandText = […]

Getting SqlDataAdapter and SqlCommand confused

I’ve got this calling method: public string RunReportSteps(int _reportKey) { DataTable fStepsTable; fStepsTable = GetStepsTable(“xxx.console.pr_xxx”); return (string)fStepsTable.Rows[1][3]; } It calls this private method: private DataTable GetStepsTable(string procName) { var connectionString = ConfigurationManager.ConnectionStrings[“xxx”].ConnectionString; using(var conn = new SqlConnection(connectionString)) using(var adapt = new SqlDataAdapter()) using(var cmd = new SqlCommand(procName, conn)) { conn.Open(); SqlParameter p = new SqlParameter(“@ReportKey”, […]

Closing SQL-connection but open connections keeps incrementing

I have the following method: public DataSet GetDataSet( string sp, params SqlParameter[] parameters ) { DataSet ds = new DataSet(); using ( SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings[“myConnectionString”].ConnectionString ) ) { using ( SqlCommand cmd = new SqlCommand() ) { cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sp; if ( parameters != null ) […]

Possible to view T-SQL syntax of a stored proc-based SqlCommand?

I was wondering if anybody knows of a way to retrieve the actual T-SQL that is to be executed by a SqlCommand object (with a CommandType of StoredProcedure) before it executes… My scenario involves optionally saving DB operations to a file or MSMQ before the command is actually executed. My assumption is that if you […]

How to use SQL Server table hints while using LINQ?

What is the way to use Sql Server’s table hints like “NOLOCK” while using LINQ? For example I can write “SELECT * from employee(NOLOCK)” in SQL. How can we write the same using LINQ?

SqlException timeout expired without being reached

From time to time our server throw this well-known exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This happens under pressure when the server is working on big requests. I did some research and found out that I could change connection string connection timeout […]

SSMS SMO Objects: Get query results

I came across this tutorial to understand how to execute SQL scripts with GO statements. Now I want to know what can I get the output of the messages TAB. With several GO statements, the output would be like this: 1 rows affected 912 rows affected … But server.ConnectionContext.ExecuteNonQuery() can return only an int, while […]

Connect ASP.NET WebSite to SQL Database

I am currently trying to establish a connection between an ASP.NET web site project and a Database built by SQL Server 2008 R2. The way I am required to do so is to use the connectionString from the Web.config page, but I have no idea what value to give it or how to establish a […]

IGNORE_DUP_KEY option in SQL Server

I did quite some search in MSDN and Google, but looks like the description for IGNORE_DUP_KEY option is very limited. My confusions, Is IGNORE_DUP_KEY option an option for a column? for a table? for a couple of columns? for an index (making index unique)? If set IGNORE_DUP_KEY to Yes, when insert a batch of records […]

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