ado.net

How can maintain a SqlConnection open always

How can maintain a SqlConnection (or using another component) open (connected) always during the execution of my .Net app? I need this because my app needs to detect using this commnad exec sp_who2 how many instances of my app are connected to mydatabase, to restrict the access (license control). example A) my app executed from ...

How can we change the DB timeout for Enterprise Library Logging block?

I need to change the DB command timeout for Enterprise Library Logging component. I have configured my Logging component to log the information in to SQL Server 2005. I don't see any option using Logger object. Is there any way through configuration, using which i can change the default timeout on database queries? ...

Converting hierarchical data into an HTML unordered list Programmatically using C#

I've data which looks something like this. | id | name | depth | itemId | +-----+----------------------+-------+-------+ | 0 | ELECTRONICS | 0 | NULL | | 1 | TELEVISIONS | 1 | NULL | | 400 | Tube | 2 | NULL | | 432 | LCD | 3 | 1653 | | 422 | Plas...

Inefficient 'ANY' LINQ clause

I have a query that pulls back a user's "feed" which is essentially all of their activity. If the user is logged in the query will be filtered so that the feed not only includes all of the specified user's data, but also any of their friends. The database structure includes an Actions table that holds the user that created the action a...

Why would it be a bad idea to have database connection open between client requests?

1) Book I’m reading argues that connections shouldn’t be opened between client requests, since they are a finite resource. I realize that max pool size can quickly be reached and thus any further attempts to open a connection will be queued until connection becomes available and for that reason it would be imperative that we release con...

how to serialize a DataTable to json or xml

Hello, i'm trying to serialize DataTable to Json or XML. is it possibly and how? any tutorials and ideas, please. For example a have a sql table: CREATE TABLE [dbo].[dictTable]( [keyValue] [int] IDENTITY(1,1) NOT NULL, [valueValue] [int] NULL, CONSTRAINT [Psd2Id] PRIMARY KEY CLUSTERED ( [keyValue] ASC )WITH (PAD_INDEX ...

how to get data from a datagridview and insert into database without using databinding?

hi guys i ve a datagrid and wen a button s clicked i want the values from the datadrid to the database.. without databind to dataset/datatable.. thanx in advance ...

Detecting duplicate values in a column of a Datatable while traversing through It

I have a Datatable with Id(guid) and Name(string) columns. I traverse through the data table and run a validation criteria on the Name (say, It should contain only letters and numbers) and then adding the corresponding Id to a List If name passes the validation. Something like below:- List<Guid> validIds=new List<Guid>(); foreach(DataR...

Searching a set of data with multiple terms using Linq

I'm in the process of moving from ADO.NET to Linq. The application is a directory search program to look people up. The users are allowed to type the search criteria into a single textbox. They can separate each term with a space, or wrap a phrase in quotes such as "park place" to indicate that it is one term. Behind the scenes the d...

Is there a transaction time out in ADO.Net which is different from command time out.

I have a large number of command executions hapening inside a single transaction. The commands are usually small such as insert/update a single row in a table. I am recieving a timeout error no matter how high I set the command time out value in ADO.Net. I am not sure if there is a transaction time out value somewhere that I am missing. ...

SqlDatasource releases any resources (such as connections) if an error occurs. Is same true for ObjectDataSource?

SqlDatasource releases any resources (such as connections) if an error occurs. Is the same true for ObjectDataSource? Thus does ODS automatically close the database connection if an error occurs? thanx ...

Why is sql server giving a conversion error when submitting date.today to a datetime column?

I am getting a conversion error every time I try to submit a date value to sql server. The column in sql server is a datetime and in vb I'm using Date.today to pass to my parameterized query. I keep getting a sql exception Conversion failed when converting datetime from character string. Here's the code Public Sub ResetOrder(ByVal con...

sqlite - any improvements for this attach code (running multiple sql commands transactionally in sqlite)

Hi, Is this code solid? I've tried to use "using" etc. Basically a method to pass as sequenced list of SQL commands to be run against a Sqlite database. I assume it is true that in sqlite by default all commands run in a single connection are handled transactionally? Is this true? i.e. I should not have to (and haven't got in the...

ADO.net - how do I reload database data into an existing & filled DataTable?

Hi, If I have an existing DataTable (already filled) but then I want to refresh/reload the data from the database directly (as I know there was a non-ADO.net process that changed data without going through the DataTable), however do I do this "refresh". Note the database data at that point can overwrite whatever is in the DataTable. t...

Executing multiple DbCommands in an open connection with Enterprise Library

How can you execute multiple DbCommands with one connection? Example: var db = DatabaseFactory.CreateDatabase(); var dbCommand = db.GetSqlStringCommand(InsertCommandText); ... db.ExecuteNonQuery(dbCommand); Now, I want to be able to Execute multiple dbCommands. For instance in pseudo kind of code: var db = DatabaseFactory.CreateData...

What would be the fastest way to insert 750 records using Ado.NET?

We have tried it using an orm mapper tool, but it opens en closes the connection 750 times. Then we tried to construct a bulk insert, but that goes even slower... Edit: CREATE TABLE [dbo].[DataWarehouse]( [DataWarehouseId] [int] IDENTITY(1,1) NOT NULL, [ColumnName] [nvarchar](max) NOT NULL, [ColumnValue] [nvarchar](max) NOT NULL, [RRN]...

insert into sql table column as GUID

I have tried with ado.net create table columnName with unique name. as uniquename I use new Guid() Guid sysColumnName = new Guid(); sysColumnName = Guid.NewGuid(); string stAddColumn = "ALTER TABLE " + tableName + " ADD " + sysColumnName.ToString() + " " + convertedColumnType + " NULL"; SqlCommand cmdAddColumn = new Sq...

Insert ADO.Net DataTable into an SQL table

The current solution i implemented is awful! I use a for... loop for inserting records from an ADO.NET data-table into an SQL table. I would like to insert at once the data-table into the SQL table, without iterating... Is that possible, or am i asking too much? ...

Does DataAdapter.Fill() close its connection when an Exception is thrown?

Hi, I am using ADO.NET (.NET 1.1) in a legacy app. I know that DataAdapter.Fill() opens and closes connections if the connection hasn't been opened manually before it's given to the DataAdapter. My question: Does it also close the connection if the .Fill() causes an Exception? (due to SQL Server cannot be reached, or whatever). Does it ...

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

Hello! 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 create a Sq...