Question Dynamically Insert single record into MS access from SQL server

[123runfast]

Hi ALL,


I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database).


My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database.


Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset.


i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically.


Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables.


-------This is My code-------------------------------------------------------


private void InsertMsiClientIntoTest(string ClientId)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
GetDataFromTablesForID(sqlserverDataset);
InsertAllTableDataIntoAccess(sqlserverDataset,tableArray);
}




private DataSet GetDataFromTablesForID(DataSet dsTablesList)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
string tableName = string.Empty;
string QueryText = string.Empty;
int i =0;
tableArray = new string[dsTablesList.Tables[0].Rows.Count];
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
foreach (DataRow itemRow in dsTablesList.Tables[0].Rows)
{
tableArray = itemRow[0].ToString();
i++;
}
foreach (string tableItem in tableArray)
{
tableName = tableItem;

QueryText = "select x.* from" + " " + tableName + " " +
"x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId";
}
sqlDataAdapter = new SqlDataAdapter(QueryText, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbExceedId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
}


return sqlserverDataset;

}


private void InsertAllTableDataIntoAccess(DataSet Inputset, string[] tableArray)
{
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["mdb"].ToString();
OleDbConnection oledbConnection = null;
OleDbDataAdapter oledbDataAdapter = null;
DataSet resultSet = new DataSet();
oledbConnection = new OleDbConnection(connectionstring);
oledbConnection.Open();
foreach (string tableItem in tableArray)
{
//????????? I NEED THE FOLLOWING CODE......////////////???????
oledbDataAdapter.InsertCommand.CommandText="insert into "
oledbDataAdapter.Fill(resultSet.Tables[0]);
}
}


Thanks in Advance...

 

[jmcilhinney]

i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs.

I'm not sure I understand that. Are you saying that you are getting one record from SQL Server and you want to insert it into multiple tables that all have the same schema in Access? If so then you can simply write one INSERT statement and change the table name each time.