• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

How would you go about INSERTING the following variables into the fields of a SQL Server 2005 table in which the table doesn't exist before the fields are inserted?

I am developing a C# web application.

I import a text file into a collection using the following code:

How would you go about INSERTING the following variables into the fields of a
SQL Server 2005 table in which the table doesn't exist before the fields are inserted?
The table is titled tbl_SMR_OI_WRS and the first few fields are as follow:

bankNum    nvarchar(4)
transYr       int
transMnth  int
transDay    int
clientAcct  nvarchar(15)
transType  nvarchar(3)
recordNum nvarchar(3)
recordNum int
processYr   nvarchar(4)

Here is the application code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace SMRWebForm
    public partial class SMRWebForm1 : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)

            //List<byte[]> result = new List<byte[]>();
            //var bytes = File.ReadAllBytes(filePath);

            List<string> ReadFile = File.ReadAllLines(@"U:\SnrMgtRpt\OI_DRS.txt").ToList();

            //manipulate data here
            foreach (string line in ReadFile)
                //do something here
                string banknum = line.Substring(0, 4);
                string transDtYR = line.Substring(4, 4);
                string transDtMnth = line.Substring(8, 2);
                string transDtday = line.Substring(10, 2);
                string officeNum = line.Substring(12, 3);
                string checkNum = line.Substring(15, 6);
                string tranType = line.Substring(21, 3);
                string recordNum = line.Substring(24, 4);
                string processDTYear = line.Substring(28, 4);
                string processDTMnth = line.Substring(32, 2);
                string processDtDay = line.Substring(34, 2);
                string select1 = line.Substring(36, 1);
                string pending = line.Substring(37, 1);
                string age = line.Substring(38, 3);
                string cr_db = line.Substring(41, 1);
                string typeRec = line.Substring(42, 1);
                string customer = line.Substring(43, 6);
                string typeAcct = line.Substring(49, 1);
                string AE = line.Substring(50, 3);
                string EX = line.Substring(53, 1);
                string descr = line.Substring(54, 30);
                string DRSFiller = line.Substring(84, 7);
                string amount = line.Substring(91, 13);
                string sign = line.Substring(104, 1);
                string refNum = line.Substring(105, 8);
                string addOff = line.Substring(113, 3);
                string comment = line.Substring(116, 24);
                string pendflag = line.Substring(140, 1);
                string userID = line.Substring(141, 8);
                string auditDate = line.Substring(149, 8);
                string auditTime = line.Substring(157, 8);
                string drsFlag = line.Substring(165, 4);
                string acctOffice = line.Substring(169, 3);
                string acctCustomer = line.Substring(172, 6);
                string typeAccount = line.Substring(178, 3);
                string acctSW = line.Substring(181, 1);
                string BKGL = line.Substring(182, 1);
1 Solution

You need to develop a customize script in Stored Procedure.

Apart from this, could you please answer below concern?

1. Is it a one time process i.e. create new fields one time or you need to create new fields based on your text files columns?
2. If it is a recurring process (i.e. new columns can come in your text files), then how you write up aforesaid code i.e. foreach loop with new string column in C# code?
3. If we need to create new fields in existing table then what should be data type? OR we need to create new fields with NVarChar(50)/VarChar(50), any thoughts?

Best Regards
Daniel Van Der WerkenIndependent ConsultantCommented:
First off, it looks like you have way more information than you need for the table insert. Also, I am not sure what you mean by the table doesn't exist beforehand. I assume the table exists, but it's not populated?

I would use SQLConnection and SQLCommand to do this work. First, you need your insert statement. It will be a constant string something like this:

const string myInsert = "INSERT INTO tbl_SMR_OI_WRS (bankNum, transYr, transMnth, transDay, clientAcct, transType, recordNum, recordNum, processYr) VALUES ('{0}', {1}, {2}, {3}, '{4}', '{5}', '{6}', {7}, '{8}'   )";

Open in new window


using (SQLConnection conn = new SQLConnection(connString))
   StringBuilder sb = new StringBuilder();
   string[] sArray = new string[9];
   sArray[0] = banknum;
   sArray[1] = transDtYR;
   sArray[2] = whatever the string value for clientacct is;
   and so forth, you get the idea, I hope.
   sb.AppendFormat( myInsert, sArray);
   string cmdText = sb.ToString();
   using (SQLCommand cmd = new SQLCommand(conn, cmdText))
      int rowsAffected = cmd.ExecuteNonQuery();

Open in new window

You would then do this insert in any way that works best for you. You could do it within the foreach loop (that might be a lot of work), or you could redo another loop and run the SqlCommand as such:

using (SqlCommand cmd = new SqlCommand(conn, string.Empty))
   StringBuilder sb = new StringBuilder();
   foreach (string line in ReadFile)
      string banknum = line.SubString(whatever);
      // set up your array.
      // clear the string builder:
     sb.Length = 0;
     sb.AppendFormat(myInsert, sArray);
     cmd.CommandText = sb.ToString();
     int rowsAffected = cmd.ExecuteNonQuery();

Open in new window

Something like that should work. Once you get the general idea, you can work with it in any way you want.

I hope this makes sense and helps.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now