Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more
You can do this in a couple of ways. One, is to build up the query yourself and execute it. SET @sql=”SELECT ” + @columnName + ‘ FROM yourTable’ sp_executesql @sql If you opt for that method, be very certain to santise your input. Even if you know your application will only give ‘real’ column … Read more
You can do this by adding an event handler to the InfoMessage event on the connection. myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage); void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e) { myStringBuilderDefinedAsClassVariable.AppendLine(e.Message); }
You need to add return parameter to the command: using (SqlConnection conn = new SqlConnection(getConnectionString())) using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = parameterStatement.getQuery(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(“SeqName”, “SeqNameValue”); var returnParameter = cmd.Parameters.Add(“@ReturnVal”, SqlDbType.Int); returnParameter.Direction = ParameterDirection.ReturnValue; conn.Open(); cmd.ExecuteNonQuery(); var result = returnParameter.Value; }
It’s pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd variable, and never use it. Here, however, you will use that instead of da.InsertCommand. Also, use a using for all disposable objects, so that you are sure that they are disposed … Read more
Something like this should work: From Frans Bouma’s Blog SELECT * FROM ( SELECT a.*, rownum r__ FROM ( SELECT * FROM ORDERS WHERE CustomerID LIKE ‘A%’ ORDER BY OrderDate DESC, ShippingDate DESC ) a WHERE rownum < ((pageNumber * pageSize) + 1 ) ) WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
I am not a fan of stored procedures Stored Procedures are MORE maintainable because: * You don’t have to recompile your C# app whenever you want to change some SQL You’ll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can … Read more
You can create a table variable to hold the result set from the stored proc and then insert the output of the stored proc into the table variable, and then use the table variable exactly as you would any other table… … sql …. Declare @T Table ([column definitions here]) Insert @T Exec storedProcname params … Read more
You can call a stored procedure in your DbContext class as follows. this.Database.SqlQuery<YourEntityType>(“storedProcedureName”,params); But if your stored procedure returns multiple result sets as your sample code, then you can see this helpful article on MSDN Stored Procedures with Multiple Result Sets
Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server. There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons. Table-Valued Parameters. SQL Server 2008 and higher only, and probably … Read more