Code for Dynamically set the database(Connection String) for the project

Code for Dynamically set the database for the project
a)In button click event you can set the databse using database properties.
b)In another button click you can access from configuration settings for SQLdataadapter

sample code in c#.net shown below:

private void button1_Click(object sender, EventArgs e)
{
Microsoft.Data.ConnectionUI.DataConnectionDialog dcd=new Microsoft.Data.ConnectionUI.DataConnectionDialog();
Microsoft.Data.ConnectionUI.DataSource.AddStandardDataSources(dcd);
if (Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
textBox1.Text = dcd.ConnectionString;
ConnectionStringsSection Section=new ConnectionStringsSection();
ConnectionStringSettings Setting;
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

Section = (ConnectionStringsSection)(config.GetSection("connectionStrings"));
if (Section.ConnectionStrings["ConString"]==null)
{

Section.ConnectionStrings.Add(new ConnectionStringSettings("ConString", dcd.ConnectionString));

}
Setting = Section.ConnectionStrings["ConString"];
Setting.ConnectionString = dcd.ConnectionString;
ConfigurationManager.RefreshSection("connectionStrings");

config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings");


}
else
{
textBox1.Text = "";
}
}

private void button2_Click(object sender, EventArgs e)
{

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select *from Testing",System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ConnectionString );
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

}

Code for How to Add Combobox Columns to datagridview in VB.Net

'Get a reference to the default DataView of the table
Dim dv As DataView = dt.DefaultView


'Create 2 combo box columns: Set DisplayMember, ValueMember and DataSource
Dim colQty1 As New DataGridViewComboBoxColumn

()
colQty1.DisplayMember = "Quantity1"
colQty1.ValueMember = "Quantity1"
colQty1.HeaderText = "Quantity 1"
colQty1.DataSource = dv

Dim colQty2 As New DataGridViewComboBoxColumn()
colQty2.DisplayMember = "Quantity2"
colQty2.ValueMember = "Quantity2"
colQty2.HeaderText = "Quantity 2"
colQty2.DataSource = dv

'Add both columns to it
dgv.Columns.AddRange(New DataGridViewColumn() {colQty1, colQty2})

SQL Server DO's and DON'Ts

SQL Server DO's and DON'Ts

So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some design guidelines for solutions using SQL Server and designing Database Access Layers (DAL): this article is for you.

Even if you are not using SQL Server, most of these design guidelines apply to other DBMS, too: Sybase is a very similar environment for the programmer, and Oracle designs may benefit from this too. I won't show here how to use specific T-SQL tricks, nor won't give you miracle solutions for your SQL Server problem. This is by no means a complete, closed issue. What I intend to do is give you some advices for a sound design, with lessons learned through the last years of my life, seeing the same design errors being done again and again.

DO know your tools.

Please, don't underestimate this tip. This is the best of all of those you'll see in this article. You'd be surprised of how many SQL Server programmers don't even know all T-SQL commands and all of those effective tools SQL Server has.

"What? I need to spend a month learning all those SQL commands I'll never use???" you might say. No, you don't need to. But spend a weekend at MSDN and browse through all T-SQL commands: the mission here is to learn a lot of what can and what can't be done. And, in the future, when designing a query, you'll remember "Hey, there's this command that does exactly what I need", and then you'll refer again to MSDN to see its exact syntax.

In this article I'll assume that you already know the T-SQL syntax or can find about it on MSDN.

DON'T use cursors

Let me say it again: DON'T use cursors. They should be your preferred way of killing the performance of an entire system. Most beginners use cursors and don't realize the performance hit they have. They use memory; they lock tables in weird ways, and they are slow. Worst of all, they defeat most of the performance optimization your DBA can do. Did you know that every FETCH being executed has about the same performance of executing a SELECT? This means that if your cursor has 10,000 records, it will execute about 10,000 SELECTs! If you can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.

Beginner SQL programmers find in cursors a comfortable and familiar way of coding. Well, unfortunately this lead to bad performance. The whole purpose of SQL is specifying what you want, not how it should be done.

I've once rewritten a cursor-based stored procedure and substituted some code for a pair of traditional SQL queries. The table had only 100,000 records and the stored procedure used to take 40 minutes to process. You should see the face of the poor programmer when the new stored procedure took 10 seconds to run!

Sometimes it's even faster to create a small application that gets all the data, proccess it and update the server. T-SQL was not done with loop performance in mind.

If you are reading this article, I need to mention: there is no good use for cursors; I have never seen cursors being well used, except for DBA work. And good DBAs, most of the time, know what they are doing. But, if you are reading this, you are not a DBA, right?

DO normalize your tables

There are two common excuses for not normalizing databases: performance and pure laziness. You'll pay for the second one sooner or later; and, about performance, don't optimize what's not slow. Often I see programmers de-normalizing databases because "this will be slow". And, more frequent than the inverse, the resulting design is slower. DBMSs were designed to be used with normalized databases, so design with normalization in mind.

DON'T SELECT *

This is hard to get used, I know. And I confess: often I use it; but try to specify only the columns you'll need. This will:

1. Reduce memory consumption and network bandwidth
2. Ease security design
3. Gives the query optimizer a chance to read all the needed columns from the indexes

DO know how your data will be/is being acessed

A robust index design is one of the good things you can do for your database. And doing this is almost an art form. Everytime you add an index to a table, things get faster on SELECT, but INSERT and DELETE will be much slower. There's a lot of work in building and mantaining indexes. If you add several indexes to a table to speed your SELECT, you'll soon notice locks being held for a long time while updating indexes. So, the question is: what is being done with this table? Reading or Updating data? This question is tricky, specially with the DELETE and UPDATE, because they often involve a SELECT for the WHERE part and after this they update the table.

DON'T create an index on the "Sex" column

This is useless. First, let's understand how indexes speed up table access. You can see indexes as a way of quickly partitioning a table based on a criteria. If you create an index with a column like "Sex", you'll have only two partitions: Male and Female. What optimization will you have on a table with 1,000,000 rows? Remember, mantaining an index is slow. Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.

DO use transactions

Specially on long-running queries. This will save you when things get wrong. Working with data for some time you'll soon discover some unexpected situation which will make your stored procured crash.

DO beware of deadlocks

Always access your tables on the same order. When working with stored procedures and transactions, you may find this soon. If you lock the table A then table B, always lock them in this very same order in all stored procedures. If you, by accident, lock the table B and then table A in another procedure some day you'll have a deadlock. Deadlocks can be tricky to find if the lock sequence is not carefully designed.

DON'T open large recordsets

A common request on programming forums is: "How can I quickly fill this combo with 100,00 items?". Well, this is an error. You can't and you shouldn't. First, your user will hate browsing through 100,000 records to find the right one. A better UI is needed here, because you should ideally show no more that 100 or 200 records to your users.

DON'T use server side cursors

Unless you know what your are doing. Client side cursors often (not always) put less overhead on the network and on the server, and reduce locking time.

DO use parametrized queries

Sometimes I see in programming forums, questions like: "My queries are failing with some chars, e.g. quotes. How can I avoid it?". And a common answer is: "Replace it by double quotes". Wrong. This is only a workaround and will still fail with other chars, and will introduce serious security bugs. Besides this, it will trash the SQL Server caching system, which will cache several similar queries, instead of caching only one. Learn how to use parameterized queries (in ADO, through the use of the Command Object, or in ADO.NET the SqlCommand) and never have these problems again.

DO always test with large databases

It's a common pattern programmers developing with a small test database, and the end user using large databases. This is an error: disk is cheap, and performance problems will only be noticed when it's too late.

DON'T import bulk data with INSERT

Unless strictly necessary. Use DTS or the BCP utility and you'll have both a flexible and fast solution.

DO beware of timeouts

When querying a database, the default timeout is often low, like 15 seconds or 30 seconds. Remember that report queries may run longer than this, specially when your database grows.

DON'T ignore simultaneous editing

Sometimes two users will edit the same record at the same time. When writing, the last writer wins and some of the updates will be lost. It's easy to detect this situation: create a timestamp column and check it before you write. If possible, merge changes. If there is a conflict, prompt the user for some action.

DON'T do SELECT max(ID) from Master when inserting in a Detail table.

This is another common mistake, and will fail when two users are inserting data at the same time. Use one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY. Avoid @@IDENTITY if possible because it can introduce some nasty bugs with triggers.

DO Avoid NULLable columns

When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check

I'm not saying that NULLs are the evil incarnation, like some people say. I believe they can have good uses and simplify coding when "missing data" is part of your business rules. But sometimes NULLable columns are used in situations like this:

CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3

This is horrible. Please, don't do this, normalize your table. It will be more flexible and faster, and will reduce the NULLable columns.

DON'T use the TEXT datatype

Unless you are using it for really large data. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better.

DON'T use temporary tables

Unless strictly necessary. Often a subquery can substitute a temporary table. They induce overhead and will give you a big headache when programming under COM+ because it uses a database connection pool and temporary tables will last forever. In SQL Server 2000, there are alternatives like the TABLE data type which can provide in-memory solutions for small tables inside stored procedures too.

DO learn how to read a query execution plan

The SQL Server query analyzer is your friend, and you'll learn a lot of how it works and how the query and index design can affect performance through it.

DO use referential integrity

This can be a great time saver. Define all your keys, unique constraints and foreign keys. Every validation you create on the server will save you time in the future.

Tips for writing efficient SQL Queries

	If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan (Rules 20-80-20)
2	Keep statistics up to date
3	Use the SHARED_CURSOR parameter
4	Use different tablespaces for tables and indexes
5	Use table partitioning (and local indexes) when appropriate (partitioning is an extra cost feature)
6	Use SQL standards and conventions to reduce parsing
7	Avoid joining too many tables
8	Use hints as appropriate
9	Avoid full table scans on large tables
10	Monitor index browning (due to deletions; rebuild as necessary)

1. Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be upto 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.

1. The code is easier to understand.

1. It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then “select * “statement can produce wrong results set and statement may fail.

• Use table alias: Always use table alias and prefix all column names with the aliases when you
• are using more than one table.

• Never compare NULL to anything else: All expressions return NULL if one of the operands is NULL. This is applicable for all operators except Concatenation operator (||).

• Use Bind Variables: It is also better to use bind variables in queries. That way the query becomes generic and therefore re-usable. For example, instead of writing query like –

o SELECT ename, sal FROM emp WHERE deptno = 20;

o Change it to –

o SELECT ename, sal FROM emp WHERE deptno = :deptno;

o The first query can be re-used for deptno number 20 only, whereas the second query can be reused for any other deptno also.

• SQL Writing Convention: It is a good practice to use a standard syntax for wiring SQL queries. We will recommend following standards to use while writing SQL queries. Write all standard SQL TEXT in upper case:

For example:

SELECT ename, sal FROM emp WHERE deptno = 20;

Write all non standard SQL TEXT (Table name, Column name etc) in lower case:

For example:

SELECT ename, sal FROM emp WHERE deptno = 20;

Note:-

It is important to write similar SQL statement in same case.

For example: Oracle will reparse following queries as they are not written in the same case

Select * from EMP;

Select * from emp;

• Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result set to contain distinct values while joining tables.

For example:

SELECT DISTINCT d.deptno, d.dname FROM dept d, emp e

WHERE d.deptno = e.deptno;

The following SQL statement is a better alternative.

SELECT d.deptno, d.dname FROM dept d WHERE EXISTS

(SELECT e.deptno FROM emp e WHERE d.deptno = e.deptno);

• Use of expressions and indexes: The optimizer fully evaluates expressions whenever possible and translates certain syntactic constructs into equivalent constructs. This is done either because Oracle can more quickly evaluate the resulting expression than the original

The original expression is merely a syntactic equivalent of the resulting expression. Any computation of constants is performed only once when the statement is optimized rather than each time the statement is executed. Consider these conditions that test for salaries greater than $2000.

sal > 24000/12

sal > 2000

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it into the second condition. Please note that optimizer does not simplify expressions across comparison operators. The optimizer does not simplify the third expression into the second. For this reason, we should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

The Optimizer does not use index for the following statement:

SELECT * FROM emp WHERE sal*12 > 24000 ;

Instead of this use the following statement:

SELECT * FROM emp WHERE sal > 24000/12 ;

• Use of NOT operator on indexed columns: Never use NOT operator on an indexed column.Whenever Oracle encounters a NOT on an index column, it will perform full-table scan.

For Example:

SELECT * FROM emp WHERE NOT deptno = 0;

Instead use the following:

SELECT * FROM emp WHERE deptno > 0;

• Function or Calculation on indexed columns: Never use a function or calculation on an indexed column. If there is any function is used on an index column, optimizer will not use index.

For Example: Do not use until need exactly match string:

SELECT * FROM emp WHERE SUBSTR (ename, 1, 3) = 'MIL';

Use following instead:

SELECT * FROM emp WHERE ename LIKE 'MIL%';

Do not use the following as || is the concatenate function. Like other functions and it disables index.

SELECT * FROM emp WHERE ename || job = 'MILLERCLERK'

Important Tricks About SQL Server and .Net Technologies...ಪಬ್ಲಿಶ್ ಬೈ - "ಹಿಬ್ಸ್"- ಹೆಚ್.ಪಿ ಚಂದ್ರಕಾಂತ್

Display Data on Datagridview(.Net) Using XML in SQL Server

Stored Procedure for Display Data Based on Different Action in SQL Server and
For generating XMLString for table ,you have to provide Action as SelectXML and TableName for this SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <>
-- Description:
-- =============================================

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ContaCommonFunctions')
BEGIN
DROP Procedure CommonFunctions
END

GO

CREATE PROCEDURE [dbo].[CommonFunctions]
@Action VARCHAR(50),
@TableName VARCHAR(50),
@IDColumnName VARCHAR(50)=NULL,
@IDColumnValue VARCHAR(50)=NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
--Select Rows based on tablename as Parameter
IF @Action='SELECT'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename and based on particular columnsname as Parameter
IF @Action='SELECTBYID'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+' WHERE '+@IDColumnName+'='''+@IDColumnValue+''''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename and columnname as Parameter
IF @Action='SELECTCOLUMN'
BEGIN
SET @SQL='SELECT '+@IDColumnName+','+@IDColumnValue+' FROM '+@TableName+''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename as Parameter
IF @Action='SELECTXML'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+' FOR XML PATH('''+@TableName+'''), ROOT(''ROOT''), TYPE, ELEMENTS XSINIL'
EXEC SP_EXECUTESQL @SQL
END

End

Ex:

EXECUTE CommonFunctions 'SELECT','TABLENAME'

OutPut: select All rows from table name which u provided
Insert and Update of table in SQL Server using XMl Parameter

Ex:For XML

EXECUTE CommonFunctions 'SELECTXML','TABLENAME'
Return:XML Format of Table

In .NET Side Code for Displaying Data on Datagrid as Follows:
dim XMLRow As String

XMLRow =GetCommonFunction("SELECTXML", "TesActions").Tables(0).Rows(0).Item(0).ToString()

DataGridView1.DataSource = FillDataSetFromXML(XMLRow).Tables(0)

--Function for Filling Dataset from XMLString
Public Function FillDataSetFromXML(ByVal XMLstr As String) As DataSet
Try
If Not XMLstr = "" Then
Dim sReader As New IO.StringReader(XMLstr)
dsXMLData.ReadXml(sReader)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return dsXMLData
End Function


Select,Insert,Update,Delete for Table Using XML in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <>
-- Description:
-- =============================================

ALTER PROCEDURE GeneralSave
@Action VARCHAR(50),
@Params XML
AS
BEGIN
SET NOCOUNT ON;


IF @Action='SELECT'
BEGIN
SELECT * FROM TesClients
END
ELSE IF @Action='SAVE'
BEGIN
IF EXISTS(SELECT *FROM 'TableName' WHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)')))
BEGIN
UPDATE TableName
SET Description=(SELECT @Params.value('(//Description)[1]','VARCHAR(30)'))
WHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)'))

END
ELSE
BEGIN

INSERT INTO TableNameSELECT (SELECT CASE WHEN MAX(ClientID)+1 IS NULL THEN 1 ELSE MAX(ClientID)+1 END FROM TesClients),
(SELECT @Params.value('(//Description)[1]','VARCHAR(30)')),'700N','CHANDRU@F.COM',0,GETDATE()
END
END
ELSE IF @Action='DELETE'
BEGIN
DELETE FROM TableNameWHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)'))
END
END
GO

Ex:
DECLARE @x XML
set @x='8BIKEConciliación Automáticasshiku0'
EXECUTE GeneralSave @x,'Save'




Massive Updation of table in SQL Server Using Temp Table and XML

CREATE PROCEDURE GeneralSaveFunction
@ParArray NVARCHAR(MAX),
@TableName VARCHAR(MAX),
@KeyColumn1 VARCHAR(MAX),
@KeyColumn2 VARCHAR(MAX) = '',
@KeyColumn3 VARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
Declare @SQL NVARCHAR(MAX)

SET @SQL = N'
IF EXISTS(SELECT * FROM sysobjects WHERE type = ''u'' and name = ''#tmp'')
BEGIN
DROP TABLE #tmp
END

DECLARE @handle int
EXEC sp_xml_preparedocument @handle OUTPUT, N''' + @ParArray + '''
SELECT * INTO #tmp FROM OPENXML (@handle, ''//item'',2) WITH ' + @TableName + '
EXEC sp_xml_removedocument @handle

Declare @Column varchar(100),@Command Nvarchar(max)
set @Command = N''Update TesActions
SET''

Declare myCursor Cursor for
select Column_Name from information_schema.columns where Table_Name = ''' + @TableName + '''
OPEN myCursor
FETCH NEXT FROM myCursor INTO @Column
WHILE @@FETCH_STATUS = 0
BEGIN
set @Command = @Command + ''
'' + @Column + '' = #tmp.'' + @Column + '' , ''

FETCH NEXT FROM myCursor INTO @Column
END
CLOSE myCursor
DEALLOCATE myCursor
select @Command = substring(@Command,0,Len(@Command)-1)
set @Command = @Command + ''
'' + ''FROM #tmp
WHERE ' + @TableName + '.' + @KeyColumn1 + ' = #TMP.' + @KeyColumn1 + ''

if (@KeyColumn2 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn2 + ' = #TMP.' + @KeyColumn2
end

if (@KeyColumn3 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn3 + ' = #TMP.' + @KeyColumn3
end

set @SQL = @SQL + '''
exec SP_executesql @Command
Drop Table #tmp'


exec sp_executesql @SQL

END
GO


DECLARE @x varchar(max)
SET @x= 'CONCILAUTOBIKEConciliación Automáticasshiku0CONCILINTEGINTEGIntegración en tesorería0CONCILMANMANConciliación Manual0'
EXECUTE GeneralSaveFunction @x,'TableName',PrimaryKeyColumnName



Replace Special Character for the string provided

--Returns special Character for the whole string
*- U Can Specify Any Special Character

SELECT REPLICATE(‘*’,LEN(‘AnyString’))

Output: ********