I need to copy one table of database to other database programmatically? : The Official Microsoft ASP.NET Forums

ASP.NET

Complete Thread

MaheshKumarC...

Member

84 Points

51 Posts

I need to copy one table of database to other database programmatically?

Apr 12, 2012 10:36 AM|LINK

I need to copy one table of database to other database and i have written below program for that

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
       <div>
        <table align="center" style="background-color:Silver; margin-top:20px;">
            <tr>
                <td width="50%">
                    <fieldset>
                        <legend>Source</legend>
                        <table>
                            <tr>
                                <td>
                                    ServerName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtservername"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    DataBaseName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtdbname"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    UserName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtuserid"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    Password:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtpwd"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                            <td>
                            Table Name:
                            </td>
                            <td>
                            <asp:TextBox runat="server" ID="txtTableName"></asp:TextBox>
                            </td>
                            </tr>
                            <tr>
                                <td colspan="2">
                                <center>
                                    <asp:Button runat="server" ID="btngetcon" Text="Copy" OnClick="get_connection" />
                                </center>
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                </td>
                <td>
                    <fieldset>
                        <legend>Destination</legend>
                        <table>
                            <tr>
                                <td>
                                    ServerName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtservername1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    DataBaseName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtdbname1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    UserName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtuserid1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    Password:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtpwd1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td colspan="2">
                                    
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                </td>
            </tr>
        </table>
    </div>
 
    </form>
</body>
</html>

and code behind

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Data; 
using System.Data.SqlClient; 
 
public partial class Default3 : System.Web.UI.Page 
{ 
    protected void Page_Load(object sender, EventArgs e) 
    { 
        
    } 
 
    protected void get_connection(object sender, EventArgs e) 
    { 
        string _sourceConnectionString = "Data Source=" + txtservername.Text.Trim() + ";Initial Catalog=" + txtdbname.Text.Trim() + ";User ID=" + txtuserid.Text.Trim() + ";Password=" + txtpwd.Text.Trim() + ";"; 
        string _destinationConnectionString = "Data Source=" + txtservername1.Text.Trim() + ";Initial Catalog=" + txtdbname1.Text.Trim() + ";User ID=" + txtuserid1.Text.Trim() + ";Password=" + txtpwd1.Text.Trim() + ";"; 
        string table = txtTableName.Text; 
        using (SqlConnection source = new SqlConnection(_sourceConnectionString))  
        {  
            string sql = string.Format("SELECT * FROM [{0}]",table); 
            SqlCommand command = new SqlCommand(sql, source); 
            source.Open();  
            IDataReader dr = command.ExecuteReader(); 
            using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString))  
            { 
                 
                copy.DestinationTableName = table;  
                copy.WriteToServer(dr);  
            } 
        }  
    } 
}

it is working if there is a table already exist in the destination database, but i want copying table structure also done through program.

Please suggest improvements this program.

Thanks...

Ch Mahesh Kumar
www.jkc-forum.in
Mark this post as answer if this helps.It can save others time.

Moved by bbcompent1 on Apr 12, 2012 10:37 AM

MaheshKumarC...

I need to copy one table of database to other database programmatically?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft