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);
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.