How do you insert a DataTable into a Database?

[horizon]

ok i'm new to this whole .net scene so i have been battling trying to find examples on how to add a newly created table into the database (connection)?
Here's what i have so far!


//create table
DataTable table = new DataTable("MyTa ble");
table.Columns.A dd(new DataColumn("Nam e", Type.GetType("S ystem.String")) );
table.Columns.A dd(new DataColumn("Age ", Type.GetType("S ystem.Byte")));

//add a row of data
DataRow dataRow = table.NewRow();
dataRow["Name"] = "John";
dataRow["Age"] = 30;
table.Rows.Add( dataRow);

//??????????????? ??????????
.
.
(Here's where i'm stuck)
How do I add my table above to the db connection below?
.
.
//??????????????? ??????????

//create database connection
OleDbConnection dbConn = new OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=" + DBFULLNAME);

------------------------------------------------------


sorry i know their must be an example somewhere but have been searching for hours with no luck at all!

thanks for any help.

[camel]

If you are asking how to get data into SQL Server that is held in a DataTable the answer would be associate an Adapter or Command to DataTable and Update.

If you are asking how to add a physical Table to a SQL Server Database you would use SMO\SQLDMO or straight TSQL, not ADO. You could also use an OleDb connection to define a new table but it would not have any data in it.

[horizon]

If you are asking how to get data into SQL Server that is held in a DataTable the answer would be associate an Adapter or Command to DataTable and Update.

If you are asking how to add a physical Table to a SQL Server Database you would use SMO\SQLDMO or straight TSQL, not ADO. You could also use an OleDb connection to define a new table but it would not have any data in it.

Well if my above example didn't show you where i'm stuck, then i'm at a loss?

All i'm after is the missing link between my DataSet and DBConnection! I've already created the database i just need to insert the damn table into it.

I looked into the DataAdapter which will allow me to supply a DataSet that the DataTable i'm using contains, but that still doesn't bring me any closer to inserting the table?

any help would be appreciated.

[horizon]

ok it appears no one understands my problem? i'll try to explain further.

1) i created a new empty database in my project folder, using SQLConfigDataSo urce (win32 api)

2) now i'd like to insert a new table using my example above in my first post, which uses "DataSet", "DataTable" , "DataColumn " and "DataRow"


I can achieve that using the following code: (but i'd much rather learn how to use the above code)

---------------------
OleDbConnection dbConn = new OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=c:\mydb. mdb");
dbConn.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = dbConn;

// create table
cmd.CommandText = "CREATE TABLE mytable (Name VARCHAR(25), Age INTEGER)";
cmd.ExecuteNonQ uery();

//add a row of data
cmd.CommandText = "INSERT INTO mytable VALUES ('John', 30)";
cmd.ExecuteNonQ uery();

dbConn.Close();
---------------------

but what's the use of having all those previous classes if i can't insert the final table into the database????


i'm sure there's a way and it's probably just a couple of lines of code but i just haven't found the right class to do it, and i'm sick of google!, if anyone could help i would really apreciate it.

[Plater]

You created the database. Great.
The question was, does the table exist in the database?

The use of those precious classes is immense, and once you get the hang of it, they're great.
You are creating a DataTable object and filling it with data.
As stated, if the table already exists in the database (will make it much easier) you can associate a simple SQL insert statement for it.

If the table does NOT exist in the database, and you want to create it at runtime, there is a lot of work to do.
You need to check to see if it's there. DROP (sql command) the table, CREATE TABLE (sql command) and THEN you can add data to it.

However, if you just want to create the table beforehand, go into your database software and create the table in there. Then you can use the simple INSERT (sql command) with your DataTable object as mentioned above.

[horizon]

You created the database. Great.
The question was, does the table exist in the database?

NO, i already stated it's a new EMPTY database.

The use of those precious classes is immense, and once you get the hang of it, they're great.

well that's what i'm trying to achieve here, and without much success so far.

You are creating a DataTable object and filling it with data.
As stated, if the table already exists in the database (will make it much easier) you can associate a simple SQL insert statement for it.

i already said i'm creating it at runtime using the following:

[DllImport("ODBC CP32.dll")]
private static extern bool SQLConfigDataSo urce(IntPtr hwndParent, ODBC fRequest, string lpszDriver, string lpszAttributes) ;

If the table does NOT exist in the database, and you want to create it at runtime, there is a lot of work to do.
You need to check to see if it's there. DROP (sql command) the table, CREATE TABLE (sql command) and THEN you can add data to it.

again, this is not what i'm asking - i can check that myself (thanks for your concern)

However, if you just want to create the table beforehand, go into your database software and create the table in there. Then you can use the simple INSERT (sql command) with your DataTable object as mentioned above.

everyone seems to keep dodging my question? i could understand if this was top-secret, but where talking about inserting a silly populated DataSet into a new empty (WITH NO TABLES) database!

it seems like there is no answer here (let alone an small example), anyway thanks again plater for your time but since i can't get a straight answer i think i'll just keep searching elsewhere (what a nightmare should of stuck with c++!!!).

[Plater]

Certainly not top secret.

http://www.google.com/search?source=ig&hl=en&rlz=&q=create+a+table+using+SQL&btnG=Google+Search

[Lokean]

Certainly not top secret.
http://www.google.com/search?source=...=Google+Search

and at least on google he won't get hit with snarky answers to his queries

[Plater]

I'm sure if you look on google long enough you will find snarky answers :-)

Snarky questions lead to snarky answers.

[camel]

Gosh this thread got carried away after what I thought was an intial "cover either question" answer ! I had to look up "snarky", not a word I would associate with.

The simple answer I believe was provided at the outset but obviously not explained clearly. You cannot create a database table directly from an ADO DataTable or DataSet, that is not the purpose of these objects which are by definition for manipulating data not defining schema. If you wish to use managed code to create a database object, table or otherwise, without using any SQL statements in the code, the use either SQL Server Management Objects (SMO), Data Management Objects (DMO) or OleDb schema objects.

[camel]

I should of course have given DMO its proper name SQLDMO, which stands for Distributed Management Objects or Database Management Objects. Anyway in .Net with SQL Server 2005 you have SMO so don't need SQLDMO.

In short the reason you can't find the answer you are looking for is the answer is "no", it makes sense to separate the classes that manipulate data from those that manipulate structure and that is what Microsoft have done.

[horizon]

(ignoring the post's above camel's)...

thank's camel for clearly explaining that i was on the wrong path even if i may of got a tad hot-headed on my last post, but that's the toll from searching long hours for a solution that never existed, btw i did finally figure that out towards the end but won't take any credit from you! thanks again and sorry for all the confusion, just wish someone would have told me sooner but no one's to blame except my inexperience.

ps: hey camel i also had to search that word, all i can say is urbandictionary (dot)com has a nice page layout :-)

[patrapathiharish]

ok i'm new to this whole .net scene so i have been battling trying to find examples on how to add a newly created table into the database (connection)?
Here's what i have so far!


//create table
DataTable table = new DataTable("MyTa ble");
table.Columns.A dd(new DataColumn("Nam e", Type.GetType("S ystem.String")) );
table.Columns.A dd(new DataColumn("Age ", Type.GetType("S ystem.Byte")));

//add a row of data
DataRow dataRow = table.NewRow();
dataRow["Name"] = "John";
dataRow["Age"] = 30;
table.Rows.Add( dataRow);

//??????????????? ??????????
.
.
(Here's where i'm stuck)
How do I add my table above to the db connection below?
.
.
//??????????????? ??????????

//create database connection
OleDbConnection dbConn = new OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=" + DBFULLNAME);

------------------------------------------------------


sorry i know their must be an example somewhere but have been searching for hours with no luck at all!

thanks for any help.

u want that code in C# or vb

[patrapathiharish]

Well if my above example didn't show you where i'm stuck, then i'm at a loss?

All i'm after is the missing link between my DataSet and DBConnection! I've already created the database i just need to insert the damn table into it.

I looked into the DataAdapter which will allow me to supply a DataSet that the DataTable i'm using contains, but that still doesn't bring me any closer to inserting the table?

any help would be appreciated.

public partial class _Default : System.Web.UI.Page 
{
    string conn = "server=balaji;database=harish;uid=sa;pwd=sqlserver";
    
    //this method for geting  selectedindexvalues for dropdownlist through sqldatasource for 
    //displaying the gridview
    protected void DDLnames_SelectedIndexChanged(object sender, EventArgs e)
    {
        SqlConnection scon = new SqlConnection(conn);
        SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", scon);
        DataSet ds = new DataSet();
        sda.Fill(ds, "tbl_names");
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
   
    //normal function
    public void FillCustomerInGrid()
    {
        SqlConnection sconn = new SqlConnection(conn);
        // SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
        SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
        DataSet ds = new DataSet();
        sda.Fill(ds, "tbl_names");
        GridView1.DataSource = ds.Tables["tbl_names"];
        GridView1.DataBind();
    }
    //update function
    public void updatCustomerInGrid()
    {
        SqlConnection sconn = new SqlConnection(conn);
       // SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
        SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
        DataSet ds = new DataSet();
        sda.Fill(ds, "tbl_names");
        GridView1.DataSource =ds.Tables["tbl_names"];
        GridView1.DataBind();
        Response.Write("<script>alert('updated')</script>");
    
    
    }
    //edit function
    public void editCustomerInGrid()
    {
        SqlConnection sconn = new SqlConnection(conn);
        SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", conn);
        //SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
        DataSet ds = new DataSet();
        sda.Fill(ds, "tbl_names");
        GridView1.DataSource = ds.Tables["tbl_names"];
        GridView1.DataBind();
     }
    //cancle function
    public void cancelCustomerInGrid()
    {
        //creation of conncetion object 
        SqlConnection sconn = new SqlConnection(conn);
        SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
        DataSet ds = new DataSet();
        sda.Fill(ds, "tbl_names");
        GridView1.Rows[0].Cells.Clear(); 
        //GridView1.DataSource = ds.Tables["tbl_names"];
        GridView1.DataBind();
     }

  

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        //to get editindex values
        GridView1.EditIndex = e.NewEditIndex;
        editCustomerInGrid();
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //creation of textbox objects
        TextBox txtnum = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtnum");
        TextBox txtnames = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtnames");
        TextBox txtbranch = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtbranch");
        TextBox txtupdate = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtupdate");
        if(txtnum=)
        {
        //creating  upadting command 
        string upcommand = "UPDATE tbl_names SET stu_num=" + txtnum.Text + ",stu_name='" + txtnames.Text + "',stu_branch='" + txtbranch.Text + "'WHERE stu_num=" + txtnum.Text +"";
        SqlConnection sconn = new SqlConnection(conn);
        sconn.Open();
        SqlCommand scmd = new SqlCommand(upcommand, sconn);  
        //give the which type of the command it is query or storedprocs
        scmd.CommandType = CommandType.Text;
        scmd.ExecuteNonQuery();
        sconn.Close();
        GridView1.EditIndex = -1;//index value
        updatCustomerInGrid();
       
        }
        }
    protected void Page_Load(object sender, EventArgs e)
    {
        //displaying the gridview 

        //SqlConnection sconn = new SqlConnection(conn);
        //SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
        //DataSet ds = new DataSet();
        //sda.Fill(ds, "tbl_names");
        //GridView1.DataSource = ds.Tables["tbl_names"];
        //GridView1.DataBind();
        
    }