Tag Archives: SQL

How to copy schema,data,objects,indexes, constraints from one database into another in MSSQL Server


In SQL Server you can easily copy values as well as schema/structure from one table to another using following commands

select * into db1.dbo.<destination table> from db2.dbo.<source table>

The limitation of this method is that, only copies the table schema , data. and leave the objects, indexes, triggers or constraints behind.

For example in the above command will copy columns to the destination table and leave identity column as regular, your new table will not hold any identity columns .

Solution

Solution to this problem is generate the scripts using Script Wizard and then execute the script to generate all objects and indexes

  • Right Click the database in your SQL Server Management Studio then choose Tasks- Generate scripts, choose a database then select objects type and review and wait for script.
  • Once it was generated go head choose and execute then script

All of the table structure,index,triggers,constraints will be created for your new database.

Now you are ready to copy data from one database from another using following insert command

insert into <destination table> (col1,col2 ...) 
select  col1,col2...  from fin_app_data.dbo.groups

Please leave identity column behind.

How to create Autocomplete Text box with SQL Data in C#.Net


As we know , can facilitate Autocomplete feature to Text box, DatagridView  controls in C#.Net.  We can also fetch auto text from data tables too, by using dataview  object.

  • To do this we need to fill the dataset and dataview objects then add the rows to string collection as follows.
GroupTableAdapter = new SqlDataAdapter("select name from AccountGroups", con);
 GroupTableDataset = new DataSet();
 GroupTableAdapter.Fill(GroupTableDataset, "AccountGroups");
 GroupTableView = new DataView(GroupTableDataset.Tables[0]);
  • Adding the rows to the string collection
AutoCompleteStringCollection autotext = new AutoCompleteStringCollection();
foreach (DataRowView row in Common.GroupTableView)
{
 autotext.Add(row["name"].ToString());
}
  • Attach the collection to the control
txt_gname.AutoCompleteMode = AutoCompleteMode.Suggest;
txt_gname.AutoCompleteSource = AutoCompleteSource.CustomSource;
txt_gname.AutoCompleteCustomSource = autotext;

drop all three section of code in load or initialize event and it will work.

 

Find rows with Find() in c#


Database programming is easy with Visual Studio.Net projects, it offers a variety of methods to visualize the programming logic. Here in our example, we demonstrate how you can extract specific rows from a TableView, which is the local representation of the SQL Table/Database Table View.

First I need a table view, like

 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
 ProductTableAdapter.Fill(ProductTableDataset, "ProductMaster");
 ProductTableView = new DataView(ProductTableDataset.Tables[0]);

Now I am going to find some rows for a specific product in the table view using the Find() method.

taekproduct="Citrizine Tab";

Common.ProductTableView.Sort="product";
int i = Common.ProductTableView.Find(takeproduct);

First I need to specify the Sort column which is the same column with the value I want to search. Second the product itself.

The Find() returns the row number instead of the row itself which can be used to access the row you want. If it’s 1 you can make sure you succeeded to find the row with takeproduct value otherwise the value will be -1.

Then I extract the tax rate from the table.

 if (i != -1)
 { takecgst = takesgst = takeigst = 0;
 double.TryParse( Common.ProductTableView[i]["cgst"].ToString(),out cgstrate);
 double.TryParse(Common.ProductTableView[i]["sgst"].ToString(), out sgstrate);
 double.TryParse(Common.ProductTableView[i]["igst"].ToString(), out igstrate);}

 

that’s all I got today.

 

 

How to make database read/write in SQL


When we have the problem with READ ONLY SQL DATABASE(It can happen while we trying to attach different version database file),  first up all we need to make the security of ldf and mdf file to everyone-Full Control.( if not working,  do the same for folder and drive in which the database file resides. )
And then simply execute the script

USE [master]

Go

Alter database [readonlydb] set READ_WRITE WITH NO_WAIT

GO

Or use the query without the NO_WAIT option.

USE [master]

Go

Alter database [readonlydb] set READ_WRITE

GO

Inserting Data rows with Parameters and Stored procedure in C# and MS SQL Server


In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,

  1. First you need to setup SqlDataAdapter
  2. Create Parameter Objects
  3. Create SQL Procedure with Parameters
Lets do one by one

SqlDataAdapter

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["MACCon"].ConnectionString;
 con.Open();
 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.

Setting up Parameters with values

ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();
with the SqlParameter  we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.

Design your  SQL Stored Procedure

Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.
ALTER PROCEDURE dbo.CRUDS_PRODUCTMASTER
 (
 @item nchar(10),
 @mfr nchar(10),
 )
 AS
 Begin
 insert into productmaster  (item,mfr) values(@item,@mfr,);
 End
The parameters in C# and SQL should match otherwise it will cause errors.

Truncate a database with sp_MSforeachtabl in MS SQL Server


There may be occasion when you need to clear all data in MS SQL Database (the most sophisticated and user friendly RDBMS available today). Here is the quick guide.

Truncate with sp_MSforeachtable Stored Procedure

Access you MS SQL Server Studio or CTP. As we all know this can be get done the job with TRUNCATE command which delete data and release physical memory too. You have to perform the command for each table in database. But the MS SQL provide a better and easy way to do the task with a stored procedure.

Start a new SQL Query and add the following command to delete all data from tables.

USE MyDatabaseEXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’

This will clear all data from table who has no foreign keys applied. You can alternatively use the following command which will only delete the contents and not the memory things.

USE MyDatabaseEXEC sp_MSforeachtable ‘DELETE FROM ?’