Hello Forum,
I am trying to understand how data is saved in data table rows from an invoice. I got this C# code on how to insert invoice data, these data is inserted into the datatable as shown below?
|
Id(int)
|
Email(nvarchar)
|
CreatedBy
|
Invoice_no
|
Item(nvarchar)
|
Qty(nvarchar)
|
Rate(int)
|
Amount(int)
|
Total(int)
|
|
1
|
|
|
|
Tables
|
5
|
1000
|
5000
|
513900
|
|
2
|
|
|
|
Chairs
|
15
|
500
|
7500
|
|
|
3
|
|
|
|
Boards
|
2
|
700
|
1400
|
|
|
4
|
|
|
|
Computers
|
10
|
50000
|
500000
|
|
May I please ask if it is possible to save data under “Item Description” “Quantity” “Rate” “Amount” in such a way that it will be saved in rows in each column.
For example if a user decides to create an invoice with 6 or 7 items, and the quantity for each item, then it will save as thus:
|
Id
|
CreatedBy
|
Item_description(navbar MAX)
|
Quantity(nvarchar MAX)
|
Rate(nvarchar MAX)
|
Amount(nvarchar MAX)
|
|
1
|
aa@gmail.com
|
Pencil, Pen, Eraser, ink, books, bags
|
3,4,1,5,6,3
|
20,50,10,25,70,600
|
60,200,10,125,420,1800
|
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("txtQuantity");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("txtRate");
Label total = (Label)Gridview1.Rows[rowIndex].Cells[4].FindControl("lblAmount");
//get the values from the TextBoxes
//then add it to the collections with a comma "," as the delimited values
sc.Add(box1.Text + "," + box2.Text + "," + box3.Text + "," + total.Text);
rowIndex++;
}
//Call the method for executing inserts
InsertRecords(sc);
}
}
}
private void InsertRecords(StringCollection sc)
{
SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
StringBuilder sb = new StringBuilder(string.Empty);
string[] splitItems = null;
foreach (string Item in sc)
{
const string sqlStatement = "INSERT INTO InvoiceTable (email,CreatedBy,Invoice_no,Item,Qty,Rate,Amount,Total) VALUES (@email,@CreatedBy,@Invoice_no,@Item,@Qty,@Rate,@Amount,@Total)";
if (Item.Contains(","))
{
splitItems = Item.Split(",".ToCharArray());
sb.AppendFormat("{0}('{1}','{2}','{3}','{4}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3]);
}
}
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
}
catch (SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
con.Close();
}
}
