Friday, March 9, 2012

Open / Read / Split / Insert / Repeat

Right now I have a web page that when you click on the only button on the page it opens a text file, reads a line from the text file, splits the comma separated line and stores it in to an array 'strData' and then moves on to the next line and repeats this process.

What I want to do before it goes to the next line is insert that strData in to a SQL DB.

This is my InsertCommand for my SqlDataSource1:
InsertCommand="INSERT INTO [Numbers] ([WTN], [DSL_Qualified], [DSL_Qualified_Date], [Equip_1MM], [Line_Trouble], [Sync_Rate], [Res_Bus], [Host_Remote]) VALUES (@.WTN, @.DSL_Qualified, @.DSL_Qualified_Date, @.Equip_1MM, @.Line_Trouble, @.Sync_Rate, @.Res_Bus, @.Host_Remote)"

Plus the <InsertParameters>:
<InsertParameters>
<asp:Parameter Name="WTN" Type="Int32" />
<asp:Parameter Name="DSL_Qualified" Type="String" />
<asp:Parameter Name="DSL_Qualified_Date" Type="DateTime" />
<asp:Parameter Name="Equip_1MM" Type="String" />
<asp:Parameter Name="Line_Trouble" Type="String" />
<asp:Parameter Name="Sync_Rate" Type="String" />
<asp:Parameter Name="Res_Bus" Type="String" />
<asp:Parameter Name="Host_Remote" Type="String" />
</InsertParameters
So I'm not sure exactly what to do after this:

while (file.Peek() > -1)
{
string strCSVData = file.ReadLine();

string [] strData = strCSVData.Split(',');

// somehow get strData in to the SqlDataSource1.Insert method??

}

Any help would be appreciated.

Thanks.

DataSource controls are meant to be databound to other controls. You can programmatically control them, but they can be a bit touchy if you do.

This is what you want:

(Written in vb.net, sorry)

Dim conn as new sqlconnection("{Your connect string here}") ' or pull from web.config

dim cmd as new sqlcommand("INSERT INTO [Numbers] ([WTN], [DSL_Qualified], [DSL_Qualified_Date], [Equip_1MM], [Line_Trouble], [Sync_Rate], [Res_Bus], [Host_Remote]) VALUES (@.WTN, @.DSL_Qualified, @.DSL_Qualified_Date, @.Equip_1MM, @.Line_Trouble, @.Sync_Rate, @.Res_Bus, @.Host_Remote)",conn)

cmd.parameters.add("@.WTN",int)

cmd.parameters.add("@.DSL_Qualified",varchar)

cmd.parameters.add("@.DSL_Qualified_Date",datetime)

{add rest of parameters here}

conn.open;

while (file.Peek() > -1)
{
string strCSVData = file.ReadLine();

string [] strData = strCSVData.Split(',');

// somehow get strData in to the SqlDataSource1.Insert method??

cmd.parameters("@.WTN").value=strData[0];

cmd.parameters("@.DSL_Qualified").value=strData[1];

{Set rest of parameters here}

cmd.executenonquery;

}

conn.close;

Your other choices if you really want to continue with the sqldatasource control, is to set the parameters using the default values, then call the .insert method of the datasource control. If that doesn't work, then you can make strData class scoped variable (Private member for C#?), call the .insert method, and catch the datasource_Inserting event, and set the sqlcommand parameters there via e.command.parameters.

No comments:

Post a Comment