Update database using DataSet and SqlDataAdapter

Here we are going to see how to update database using DataSet and SqlDataAdapter.

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

namespace UpdateDatabase

{

public class TestDataSet : System.Windows.Forms.Form

{

private System.Windows.Forms.DataGrid dataGrid1;

private System.Windows.Forms.Button btnLoad;

private System.Windows.Forms.Button btnUpdate;

private DataSet ds;

private SqlDataAdapter adap;

private SqlConnection con;

private System.Windows.Forms.Label lblTitle;

private System.Windows.Forms.TextBox txtTitle;

private System.ComponentModel.Container components = null;

public TestDataSet()

{

InitializeComponent();

con = new SqlConnection("server=.;uid=sa;pwd=test;database=test");}

///

/// Clean up any resources being used.

///

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.dataGrid1 = new System.Windows.Forms.DataGrid();

this.btnUpdate = new System.Windows.Forms.Button();

this.btnLoad = new System.Windows.Forms.Button();

this.txtTitle = new System.Windows.Forms.TextBox();

this.lblTitle = new System.Windows.Forms.Label();

((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();

this.SuspendLayout();

//

// dataGrid1

//

this.dataGrid1.DataMember = "";

this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;

this.dataGrid1.Location = new System.Drawing.Point(16, 80);

this.dataGrid1.Name = "dataGrid1";

this.dataGrid1.Size = new System.Drawing.Size(568, 280);

this.dataGrid1.TabIndex = 0;

//

// btnUpdate

//

this.btnUpdate.Location = new System.Drawing.Point(176, 369);

this.btnUpdate.Name = "btnUpdate";

this.btnUpdate.Size = new System.Drawing.Size(112, 23);

this.btnUpdate.TabIndex = 1;

this.btnUpdate.Text = "Update Title";

this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);

//

// btnLoad

//

this.btnLoad.Location = new System.Drawing.Point(504, 56);

this.btnLoad.Name = "btnLoad";

this.btnLoad.TabIndex = 2;

this.btnLoad.Text = "Load";

this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);

//

// txtTitle

//

this.txtTitle.Location = new System.Drawing.Point(64, 372);

this.txtTitle.Name = "txtTitle";

this.txtTitle.TabIndex = 3;

this.txtTitle.Text = "";

//

// lblTitle

//

this.lblTitle.AutoSize = true;

this.lblTitle.Location = new System.Drawing.Point(16, 376);

this.lblTitle.Name = "lblTitle";

this.lblTitle.Size = new System.Drawing.Size(26, 16);

this.lblTitle.TabIndex = 4;

this.lblTitle.Text = "Title";

//

// TestDataSet

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(616, 438);

this.Controls.Add(this.lblTitle);

this.Controls.Add(this.txtTitle);

this.Controls.Add(this.btnLoad);

this.Controls.Add(this.btnUpdate);

this.Controls.Add(this.dataGrid1);

this.Name = "TestDataSet";

this.Text = "Data";

((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

this.ResumeLayout(false);

}

#endregion

///

/// The main entry point for the application.

///

[STAThread]

static void Main()

{

Application.Run(new TestDataSet());

}

private void btnLoad_Click(object sender, System.EventArgs e)

{

LoadData();

}

//Load data from database make sure we are fetching primarykey too so we could use adapter

update method

//on dataset because commandbuilder will create command on that primary key.

//Commands will be created as it see rowstate of dataset table's row's rowstate.

private void LoadData()

{

if(ds != null)

ds.Clear();

adap = new SqlDataAdapter("select id,title, description from testtable", con);

ds = new DataSet();

adap.Fill(ds);

dataGrid1.DataSource = ds.Tables[0];

}

//This click will update one of the field in the database using adapter update() method on

dataset.

private void btnUpdate_Click(object sender, System.EventArgs e)

{

SqlCommandBuilder com = new SqlCommandBuilder(adap);

foreach(DataRow dr in ds.Tables[0].Rows)

dr["title"] = txtTitle.Text;

adap.Update(ds);

}

}

}

Pass Array to a Stored Procedure

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.

CREATE PROCEDURE [dbo].[GetData]

@MyCodes as varchar(500) = '', -- comma delimited list of codes, ie: '''ABC'', ''DEF'', ''GHI'''

AS

BEGIN

DECLARE @query as nvarchar(500)

set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

exec SP_EXECUTESQL @query,

N'@p_MyCodes varchar(500)',

@p_MyCodes = @MyCodes

END

The above stored procedure definition will accept a comma-delimited string, which we process as an array using the SQL IN command. Note, we had to use dyanmic SQL to properly form the query (which involves expanding the comma-delimited string).

Simple Method to Pass Array to a Stored Procedure - C# .NET Side

Next, we need to define the method to pass the data and execute the stored procedure from C# .NET.

The first step is to convert our array of data into a comma-delimited string, which is what the stored procedure expects to receive. Depending on your data type, this code may vary. For this example, we are using a .NET collection.

string myCodes = string.Empty; // Initialize a string to hold the comma-delimited data as empty

foreach (MyItem item in MyCollection)

{

if (myCodes.Length > 0)

{

myCodes += ", "; // Add a comma if data already exists

}

myCodes += "'" + item.Name + "'";

}

The code above will create a string in the following format:

'One','Two','Three'

Now that the collection has been converted to a string, we can pass the value as a parameter to the stored procedure by using the following code:

using System;

using System.Data;

using System.Data.SqlClient;

SqlConnection MyConnection = null;

SqlDataReader MyReader = null;

try

{

// Create the SQL connection.

MyConnection = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))

MyConnection.Open();

// Create the stored procedure command.

SqlCommand MyCommand = new SqlCommand("GetData", MyConnection);

// Set the command type property.

MyCommand.CommandType = CommandType.StoredProcedure;

// Pass the string (array) into the stored procedure.

MyCommand.Parameters.Add(new SqlParameter("@MyCodes", myCodes));

// Execute the command

MyReader = MyCommand.ExecuteReader();

// ...

}

catch (Exception excep)

{

}

finally

{

if (MyReader != null)

{

MyReader.Close();

MyReader.Dispose();

}

if (MyConnection != null)

{

MyConnection.Close();

MyConnection.Dispose();

}

}

Export SQL data to CSV/TXT/XLS

The BCP command is used to export sql server table/view to files(Text, Csv and Excel). But the BCP command doesn't product the header (Column Names).

1) To export data to new CSV file with heading(column names), create the following procedure:

CREATE PROCEDURE dbo.proc_generate_csv

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

) AS

Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),

@x varchar(300)

--Generate column names as a recordset

Select @Headers = IsNull(@Headers + ',', '') + Column_Name

From INFORMATION_SCHEMA.COLUMNS

Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

print @Headers

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -C RAW -t "," -r \n'

print @sql

exec master..xp_cmdshell @sql

set @sql = 'exec master..xp_cmdshell ' + @sql

print @sql

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

print @data_file

set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -C RAW -t "," -r \n'

print @sql

exec master..xp_cmdshell @sql

--Copy dummy file to passed CSV file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

print @sql

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

print @sql

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_csv 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_csv 'Northwind', 'Products','C:\Products.csv'

2) To export data to new Excel file with heading(column names), create the following procedure:

CREATE procedure proc_generate_excel

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select

@columns=coalesce(@columns+',','')+column_name

from

information_schema.columns

where

table_name=@table_name

select @columns = '''''' + replace(@columns,',',''''',''''') + ''''''

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file

set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@file_name+'" -c'''

exec(@sql)

--Generate data in the dummy file

set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''

exec(@sql)

--Copy dummy file to passed EXCEL file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_excel 'Northwind', 'Products','C:\Products.xls'

3) To export data to new Text file with heading(column names), create the following procedure:

CREATE PROCEDURE dbo.proc_generate_txt

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

) AS

Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),

@x varchar(300)

--Generate column names as a recordset

Select @Headers = IsNull(@Headers + CHAR(9) ,'') + Column_Name

From INFORMATION_SCHEMA.COLUMNS

Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

print @Headers

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -t'

print @sql

exec master..xp_cmdshell @sql

set @sql = 'exec master..xp_cmdshell ' + @sql

print @sql

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

print @data_file

set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -t"\t"'

print @sql

exec master..xp_cmdshell @sql

--Copy dummy file to passed txt file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

print @sql

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

print @sql

exec(@sql)

GO

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_txt 'your dbname', 'your table name/Your View Name','your file path'

E.g

EXEC proc_generate_txt 'Northwind', 'Products','C:\Products.txt'