CODEWITHSUNDEEP
c#sql-serverdatabasesqlcommand
coding
coding

Reputation: 63

How to insert a row at a specific index in a SQL database table using C#

I have created a SQL database named: DateTimes.

The database is now filled in with this information seen on this image: See the database table here

As seen in the DateTime column, we can see those DateTime values in ascending order:

201005011600
201005011630
201005011645
201005011700

Now comes the problem, I wonder how to do. As we can see the rows are in DateTime ascending order and now I have a new record that have this DateTime that I want to add to the table:
201005011615

The code I have so far below only ADDS a row at the very end of the table.

I do have 2 questions here:

  1. I now want to insert this record in the correct row which will be the second row in the DataTable. Which means that this record will be inserted at the correct index in the Table (DateTime ascending order)
    How is this possible to do?

  2. Looking at the code, I use: comm.Parameters.Add(new SqlParameter... 5 times. What I wonder here is since I could have many thousands of columns later. If there will be a faster way to .Add all info for all columns here in some kind of batch here instead. Which would mean, 1 .Add, instead of 5 .Adds?

     void insertvalue()
 {
     string connectionString = GetConnectionString();
     string cmdString = "INSERT INTO DateTimes (DateTime,F1,F2,G1,G2) VALUES (@val1, @val2, @val3, @val4, @val5)";
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         using (SqlCommand comm = new SqlCommand())
         {
             comm.Connection = conn;
             comm.CommandText = cmdString;
             comm.Parameters.Add(new SqlParameter("@val1", 201005011615));
             comm.Parameters.Add(new SqlParameter("@val2", 0.05044));
             comm.Parameters.Add(new SqlParameter("@val3", 0.05044));
             comm.Parameters.Add(new SqlParameter("@val4", 0.05044));
             comm.Parameters.Add(new SqlParameter("@val5", 0.05044));
             try
             {
                 conn.Open();
                 int i = comm.ExecuteNonQuery();
                 if (i != 0) { MessageBox.Show(i + "Data Saved"); }
             }
             catch (SqlException ex) { MessageBox.Show(ex.ToString()); }
         }
     }
 }
 static private string GetConnectionString()
 {
     return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\source\\repos\\TestDatabaseCreation\\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
 }

Upvotes: 0

Views: 1030

Answers (1)

Martin Cairney
Martin Cairney

Reputation: 1767

Think about how you will query the data in that table. If it is the case that you will ALWAYS want to retrieve the data in DATE order - and also that the DATE is always a reflection of the date and time that you inserted the row, then defining a CLUSTERED index on the DATE column would be appropriate.

You will then generally always add rows to the end of the current page (and then get the next contiguous page on disk) and keep adding to the end of that. Your queries by DATE will be efficient as they will be able to find a starting page and scan contiguously from there to the end of your date range.

NOTE - SQL Server will ALWAYS read the page into memory first before sending it to your client application - that's just the way it works. There is no mechanism to stream the pages from disk directly to your client application. But, don't worry about that as it is as efficient a process as you can get.

Upvotes: 0

Related Questions