how to store the .dat file containing white space between the text and the sql server database in rows and columns as it is


c# how to store .dat file containing white spaces between text to sql server database in rows and columns as it is

1   2014-03-13 15:21:32 1   0   1   0
24  2015-02-09 16:37:52 1   0   1   0
16  2015-02-09 16:40:10 1   0   1   0
11  2015-02-09 16:41:17 1   0   1   0

If you just want to store the .dat File to recreate and use it later on, you can store it as a BLOB:


   SqlParameter param=new SqlParameter("@yourdatFile", SqlDbType.VarBinary);

Your sql field will be varbinary(max). Later on read it back from DB as Binary and stream it into a file.

If your goal is to load the information between the whitespaces in your dat-file into separate columns in your database table or even create a new table dynamically with different count of columns for further use, you should read your file first via Split, then you can dynamically create your table and insert.

For example:

        var f = File.ReadAllText("path");
        var splitted = f.Split(null); // splits by whitespace

        // If you want to create a dynamic table for your .dat file information, since you don't know how many columns will be needed.
        // I do not recommend this way!! Your database scheme should not be modified dynamically.
        var createSql = "CREATE TABLE yourTable ( ";
        foreach (var entry in splitted)
            createSql += string.Format("{0} nvarchar(100), ", entry);

        createSql += " );";

        var insertSql = "INSERT INTO yourTable VALUES ( ";
        foreach (var entry in splitted)
            insertSql += string.Format("{0}, ", entry);

        insertSql += " );";

        // run these two scripts via ado on your DB