G
Guest
NEW Post
Here's my best guess at how to insert this dataset....
the code runs, but no new records are added to the sql table.
I've read and split a delimited text file into a dataset.
It looks fine in a datagrid (5 columns and 5,000 rows),
but I've been trying, without success, to then insert
the resulting dataset called "result" into a single sql
table that has an auto-increment and PK column called ID,
as well as the 5 columns from the dataset.
Any suggestions on a way to perform the insert of the
"result" dataset into the sql table?
Thanks,
Paul
==================================================================================================
StreamReader sr = new StreamReader("C:\\test.txt"); //Read From A
File instead of a webrequest
DataSet result = new DataSet(); //The DataSet to Return
result.Tables.Add("MyNewTable"); //Add DataTable to hold the DataSet
result.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a single
column to the DataTable
result.Tables["MyNewTable"].Columns.Add("FormType"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("CIK"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a single
column
string AllData1 = sr.ReadToEnd(); //Read the rest of the
data in the file.
string[] rows = AllData1.Split("\n".ToCharArray()); //Split off each
row at the Line Feed
foreach(string r in rows) //Now add each row to the
DataSet
{
string delimStr1 = "\t";
string[] items = r.Split(delimStr1.ToCharArray()); //Split the row at the
delimiter
result.Tables["MyNewTable"].Rows.Add(items); //Add the item
}
for (int i = 1; i <= 11; i++) //Remove first 8
rows from the DataTable/DataSet
{
result.Tables["MyNewTable"].Rows.RemoveAt(0);
}
dataGrid1.SetDataBinding(result, "MyNewTable"); //Binds DataGrid to
DataSet,displaying datatable
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(); //Configure a
data adapter
//Configure the SQL connection
SqlConnection sqlConnection1 = new SqlConnection("workstation id=AMD;packet
size=4096;" +
"integrated security=SSPI;data source=AMD;persist security
info=False;initial catalog=SEC_XBRL_10");
//Generate the 'INSERT' command
SqlCommand sqlInsertCommand1 = new SqlCommand("INSERT INTO
SEC_Index_01(CompanyName, FormType, CIK, " +
"DateFiled, SECWebAddress) VALUES (@CompanyName, @FormType, @CIK,
@DateFiled, @FileName); SELECT ID, " +
"CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
WHERE (ID = @@IDENTITY)", sqlConnection1);
//Add the parameters
sqlInsertCommand1.Parameters.Add("@CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName");
sqlInsertCommand1.Parameters.Add("@FormType", System.Data.SqlDbType.VarChar,
8000, "FormType");
sqlInsertCommand1.Parameters.Add("@CIK", System.Data.SqlDbType.VarChar,
8000, "CIK");
sqlInsertCommand1.Parameters.Add("@DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled");
sqlInsertCommand1.Parameters.Add("@FileName", System.Data.SqlDbType.VarChar,
8000, "SECWebAddress");
sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Set the insert
command
sqlDataAdapter1.Update(result,"SEC_Index_01"); //Perform the
update
Here's my best guess at how to insert this dataset....
the code runs, but no new records are added to the sql table.
I've read and split a delimited text file into a dataset.
It looks fine in a datagrid (5 columns and 5,000 rows),
but I've been trying, without success, to then insert
the resulting dataset called "result" into a single sql
table that has an auto-increment and PK column called ID,
as well as the 5 columns from the dataset.
Any suggestions on a way to perform the insert of the
"result" dataset into the sql table?
Thanks,
Paul
==================================================================================================
StreamReader sr = new StreamReader("C:\\test.txt"); //Read From A
File instead of a webrequest
DataSet result = new DataSet(); //The DataSet to Return
result.Tables.Add("MyNewTable"); //Add DataTable to hold the DataSet
result.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a single
column to the DataTable
result.Tables["MyNewTable"].Columns.Add("FormType"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("CIK"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a single
column
result.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a single
column
string AllData1 = sr.ReadToEnd(); //Read the rest of the
data in the file.
string[] rows = AllData1.Split("\n".ToCharArray()); //Split off each
row at the Line Feed
foreach(string r in rows) //Now add each row to the
DataSet
{
string delimStr1 = "\t";
string[] items = r.Split(delimStr1.ToCharArray()); //Split the row at the
delimiter
result.Tables["MyNewTable"].Rows.Add(items); //Add the item
}
for (int i = 1; i <= 11; i++) //Remove first 8
rows from the DataTable/DataSet
{
result.Tables["MyNewTable"].Rows.RemoveAt(0);
}
dataGrid1.SetDataBinding(result, "MyNewTable"); //Binds DataGrid to
DataSet,displaying datatable
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(); //Configure a
data adapter
//Configure the SQL connection
SqlConnection sqlConnection1 = new SqlConnection("workstation id=AMD;packet
size=4096;" +
"integrated security=SSPI;data source=AMD;persist security
info=False;initial catalog=SEC_XBRL_10");
//Generate the 'INSERT' command
SqlCommand sqlInsertCommand1 = new SqlCommand("INSERT INTO
SEC_Index_01(CompanyName, FormType, CIK, " +
"DateFiled, SECWebAddress) VALUES (@CompanyName, @FormType, @CIK,
@DateFiled, @FileName); SELECT ID, " +
"CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
WHERE (ID = @@IDENTITY)", sqlConnection1);
//Add the parameters
sqlInsertCommand1.Parameters.Add("@CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName");
sqlInsertCommand1.Parameters.Add("@FormType", System.Data.SqlDbType.VarChar,
8000, "FormType");
sqlInsertCommand1.Parameters.Add("@CIK", System.Data.SqlDbType.VarChar,
8000, "CIK");
sqlInsertCommand1.Parameters.Add("@DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled");
sqlInsertCommand1.Parameters.Add("@FileName", System.Data.SqlDbType.VarChar,
8000, "SECWebAddress");
sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Set the insert
command
sqlDataAdapter1.Update(result,"SEC_Index_01"); //Perform the
update