I got 10 tables in SQL server and I want to export those table values to a ms access database which also contains 10 tables. so is there any way to export data using c# code. I don't want to write lots of code for each table to get data from a particular table then insert row by row to the respective access table is there any shortcut available that I can use please do let me know.
-
You didn't mention anything about what you have tried so far.dotNET– dotNET2016-09-15 06:51:51 +00:00Commented Sep 15, 2016 at 6:51
-
You also seemingly haven't done any search on the Internet. This question has been answered on SO before.dotNET– dotNET2016-09-15 06:54:02 +00:00Commented Sep 15, 2016 at 6:54
Add a comment
|
4 Answers
i thik this code help you.
OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"Database.accdb\";Persist Security Info=False;");
//command to insert each ASIN
OleDbCommand cmd = new OleDbCommand();
//command to update each column (ASIN, Retail... from CSV)
OleDbCommand cmd1 = new OleDbCommand();
//load csv data to dtCSV datatabe
DataTable dtCSV = new DataTable();
dtCSV = ds.Tables[0];
// Now we will collect data from data table and insert it into database one by one
// Initially there will be no data in database so we will insert data in first two columns
// and after that we will update data in same row for remaining columns
// The logic is simple. 'i' represents rows while 'j' represents columns
cmd.Connection = myConnection;
cmd.CommandType = CommandType.Text;
cmd1.Connection = myConnection;
cmd1.CommandType = CommandType.Text;
myConnection.Open();
for (int i = 0; i <= dtCSV.Rows.Count - 1; i++)
{
cmd.CommandText = "INSERT INTO " + lblTable.Text + "(ID, " + dtCSV.Columns[0].ColumnName.Trim() + ") VALUES (" + (i + 1) + ",'" + dtCSV.Rows[i].ItemArray.GetValue(0) + "')";
cmd.ExecuteNonQuery();
for (int j = 1; j <= dtCSV.Columns.Count - 1; j++)
{
cmd1.CommandText = "UPDATE " + lblTable.Text + " SET [" + dtCSV.Columns[j].ColumnName.Trim() + "] = '" + dtCSV.Rows[i].ItemArray.GetValue(j) + "' WHERE ID = " + (i + 1);
cmd1.ExecuteNonQuery();
}
}
myConnection.Close();
3 Comments
JeetDaloneboy
No this wont help me. i have stored all the tables into dataset. now i want these tables values to be inserted into respective tables of ms access. i don't want to insert row by row i just want to dump the whole table values into ms access table values.
dotNET
@JeetDaloneboy: DataSet doesn't work this way. DataSets are generally strongly typed. Even if they're not in your case, they need CRUD commands (which again work at row level) for each table to perform DDL. At the very least you need a transformation function between each pair of corresponding SQL Server and Access tables.
JeetDaloneboy
cant i dump datatable values to a access table ?
If this is a one-off operation, you should use Data Export features of SQL Server or import features of Access to perform this. They'll be simple enough for end-users who don't want to write code. Therein you simply select the database, the tables that you want to export and the destination database and everything will get exported on click of a button.
3 Comments
JeetDaloneboy
But I want my custom table and column names in ms access.
dotNET
Once you have your data in Access, you can rename things there.
JeetDaloneboy
I am just following the requirements cant do this.
First make sure that the access table columns have the same column names and similar types. Then you can use this function which I believe is very fast and elegant.
public void AccessBulkCopy(DataTable table)
{
foreach (DataRow r in table.Rows)
r.SetAdded();
var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn);
var cbr = new OleDbCommandBuilder(myAdapter);
cbr.QuotePrefix = "[";
cbr.QuoteSuffix = "]";
cbr.GetInsertCommand(true);
myAdapter.Update(table);
}
use this code
1 Comment
JeetDaloneboy
the problem is access table names and column names are different from sql table and column names. :(
Well i got the answer. i used for loop to generate query string like below.
public void MainAccess(int _i)
{
DataTable dt = ds.Tables[_i];
string sql = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = sql + "INSERT INTO "+ _tableString[_i] + " values('";
for (int j = 0; j < dt.Columns.Count; j++)
{
sql += dt.Rows[i][j].ToString().Trim();
if (j != dt.Columns.Count - 1)
{
sql += "','";
}
else
{
sql += "')";
}
}
ExecuteQuery(sql);
sql = null;
}
}