Tag: ado.net

Hierarchy in SQL and ASP.net

I am creating a web page which contains Posts and Comments. When I retrieve the Comments for each Post during the rendering of that Post, however, the retrieval is very slow. My guess is that this is due to opening/closing connections frequently. Regardless, it doesn’t scale well. To fix this, I am considering merging the […]

Delete from table, and return the records which weren't deleted

I have an table at my SQLSERVER(2008R2) called “Clients” , it has ClientID and Address. I’d like to make a query/SP which does the following: Recieves a list of addresses. Deletes the records with these address. Returns a list of the addresses that can’t be deleted(row not found, etc). Returns the number of deleted rows. […]

Creating Dataset Dynamically and passing to ReportViewer

all i did is just create a reportViewer in the form, then i have this code: SqlConnection cn = new SqlConnection(@”Data Source=.\SQLEXPRESS;AttachDbFilename=G:\I.S\C#\billingSystem\Store.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True”); private void Form1_Load() { runRptViewer(); cn.Open(); } private void rptGetDataset() { DataSet ds = new DataSet(); ds.DataSetName = “dsNewDataSet”; SqlDataAdapter da = new SqlDataAdapter(“SELECT * FROM NewBill”, cn); ds.GetXmlSchema(); da.Fill(ds); […]

while passing multiple integer value together getting error

I have a stored procedure like this: ALTER PROCEDURE [dbo].[T_TransactionSummary] @locations integer AS BEGIN ………….. …………. AND (Location_tbl.Locid IN (@locations)) my locid field is integer this locid coming from my listbox.if i select one item 1 locid will come.if i select 2 item 2 locid will come.. I’ve got a ListBox which populates the @locations […]

Insert Records in SQL Server from Oracle C#

I am working on winform application in C# where I have to import some 200,000 records from an Oracle view and dump into SQL Server table. I am not sure how to approach this problem whether i should use datatable to hold all these records and then store it in SQL server Or I have […]

dataTable as parameter to Function

I have SQL SP’s that I use to populate DataTables in VB and those to fill listbox/charts etc in VB form. In VB I am trying to use OOP and thus use classes for variables. In VB I have a function with the code to pull the data using a SP, this function I want […]

SqlCommand.ExecuteReader transfers data on Close even without a single Read

Given a database connection via TCP When I execute following code Then I see in Task Manager that SQL Server always sends the queried data (when the Reader closes) over the network – even when we do not Read/Fetch them! var command = new SqlCommand(“SELECT TOP 100 Stamp, Blob”, connection) { CommandTimeout = commandTimeout }; […]

How can I count rows returned by the stored procedure?

I must load a large set of data from SQL Server with ado.net. This data returned from a slow stored procedure. I can’t store result in temporary table because I don’t know structure of result table. So I cannot do something like: CREATE TABLE #temp ( ID INT, NAME VARCHAR(100), … ) INSERT INTO #temp […]

define per connection variables for sql server

How can I achieve something like following SqlConnection connection = new SqlConnection(“CNStr”); SqlCommand Command = connection.CreateCommand(); command.CommandText = “DECLARE @find varchar(30) = ‘Test’; “; command.ExecuteNonQuery(); command.CommandText = “select * from Some_View_Which_Has_Used_That_Variable_In_ItsWhere_Condition” var result = command.ExecuteNonQuery(); command.Dispose(); connection.Dispose(); I want to declare some per connection variable, and then use them everywhere, in views, functions and so […]

Does Sql Server cache stored procedure execution plans when calling from c#?

I am executing a stored procedure with one parameter in a foreach loop. I get the result and do some extra processing after that. Here’s the code: foreach(var id in GetIds()) { var result = ExecuteStoreProcedureForResult(id); //do extra processing with the result } When I execute the sp in directly from Query Analyzer the first […]

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