How to insert a 2D Array into Database using c#

Question

I have a two dimensional array with 3 columns and 2 rows. I also have a database table with 3 columns. I want to insert the 2D array directly into the database.

Is there any way to that?

Any help is appreciated. I can supply more details if needed.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _2DArrayIntoDatabaseTest
{

public partial class Form1 : Form
{

 string[,] LoginInfo = new string[2, 3]{{"1", "Admin", "123"},{"2", "Admin2", "456"}};
 string query;
 SqlCommand Sqlcmd;
 SqlConnection conn = new SqlConnection(@"Data Source=MIRAZ-PC\SQLEXPRESS;
                      Initial Catalog=2DArrayIntoDatabaseTest;
                      Integrated Security=True");
 DataTable dbdataset;

 public Form1()
 {
  InitializeComponent();
 }

 private void Form1_Load(object sender, EventArgs e)
 {
  this.tTableAdapter.Fill(this._2DArrayIntoDatabaseTestDataSet.t);
 }

 int i = 0, j = 0;

 private void button1_Click(object sender, EventArgs e)
 {

  try
  {

   for (i = 0; i < 2; i++)
   {

    for (j = 0; j < 3;j++ )
     query = "INSERT INTO t(SerialNumber,UserName,Password) 
             values( '" + LoginInfo[i, 0] + "','" 
                        + LoginInfo[i, 1] + "','" 
                        + LoginInfo[i, 2] + "')";
   }

   Sqlcmd = new SqlCommand(query, conn);
   conn.Open();
   Sqlcmd.ExecuteNonQuery();
   conn.Close();

  }
  catch(Exception ex)
  {
   MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
  }

  try
  {

   query = "SELECT * from t";
   Sqlcmd = new SqlCommand(query, conn);
   conn.Open();
   SqlDataAdapter sda = new SqlDataAdapter();
   sda.SelectCommand = Sqlcmd;
   dbdataset = new DataTable();
   sda.Fill(dbdataset);
   BindingSource bSource = new BindingSource();
   bSource.DataSource = dbdataset;
   dataGridView1.DataSource = bSource;
   sda.Update(dbdataset);
   //dataGridView1.Columns.Remove("rownum");

  }
  catch (Exception ex)
  {
   MessageBox.Show(ex.Message);
  }
  finally
  {
   conn.Close();
  }
}
}
}

Now this piece of code compiles fine. But in Data Grid View I can only see 1 row instead of 2.

How to solve that?

Note: Here I am trying to use a nested loop to create a dynamic query to insert a row of data one at a time.

Answer 1

Instead of [i, 1], [i, 2], and [i, 3] you need [i, 0], [i, 1], and [i, 2]. Also, ExecuteNonQuery() needs to happen inside the for loop.

While I'm here, I'll also show some better practice on including data in with the SQL query. The current code is crazy-vulnerable to sql injection.

private void button1_Click(object sender, EventArgs e)
{
    string query = "INSERT INTO t(SerialNumber,UserName,Password) VALUES (@serial, @user, @pass);";
    var dbdataset = new DataTable();

    //ADO.Net does better if you create new objects, rather than try to re-use them through a class or application.
    // The "using" blocks will make sure things are closed and disposed properly, even if an exception is thrown
    using (var conn = new SqlConnection(@"Data Source=MIRAZ-PC\SQLEXPRESS;Initial Catalog=2DArrayIntoDatabaseTest;Integrated Security=True"))
    using (var cmd = new SqlCommand(query, conn))
    {   
        //I had to guess at column types and lengths here.
        // You should use actual column types and lengths from the DB
        cmd.Parameters.Add("@serial", SqlDbType.NVarChar, 20);
        cmd.Parameters.Add("@user", SqlDbType.NVarChar, 20);  
        cmd.Parameters.Add("@pass", SqlDbType.NVarChar, 20);
        conn.Open();

        for (i = 0; i < LoginInfo.GetUpperBound(0); i++)
        {
            cmd.Parameters["@serial"].Value = LoginInfo[i, 0];
            cmd.Parameters["@user"].Value = LoginInfo[i, 1];
            cmd.Parameters["@pass"].Value = LoginInfo[i, 2];

            try
            {
                //don't forget to do this INSIDE the loop
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        cmd.CommandText = "SELECT * FROM t";
        var sda = new SqlDataAdapter(cmd);

        try
        {
            sda.Fill(dbdataset);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    dataGridView1.DataSource = dbdataset;
}

Last of all... plain-text passwords like this are NOT GOOD.

---

Here's an example using a List<UserLoginInfo>. Note moving code to the new DB class here is not required for the List to work; it's just good practice to do that anyway.

public class UserLoginInfo
{
    public string SerialNumber {get;set;} //you might want an int here instead
    public string Username {get;set;}
    public string Password {get;set;}
}

public static class DB
{
    private static readonly string ConnectionString = @"Data Source=MIRAZ-PC\SQLEXPRESS;Initial Catalog=2DArrayIntoDatabaseTest;Integrated Security=True";

    public static void SaveUserData(IEnumerable<UserLoginInfo> users)
    {
        string query = "INSERT INTO t(SerialNumber,UserName,Password) VALUES (@serial, @user, @pass);";

        using (var conn = new SqlConnection(ConnectionString))
        using (var cmd = new SqlCommand(query, conn))
        {   
            cmd.Parameters.Add("@serial", SqlDbType.NVarChar, 20);
            cmd.Parameters.Add("@user", SqlDbType.NVarChar, 20);  
            cmd.Parameters.Add("@pass", SqlDbType.NVarChar, 20);
            conn.Open();

            foreach(var user in users)
            {
                cmd.Parameters["@serial"].Value = user.SerialNumber;
                cmd.Parameters["@user"].Value = user.UserName;
                cmd.Parameters["@pass"].Value = user.Password;
                cmd.ExecuteNonQuery();
            }
        }
    }

    public static DataTable GetLoginData()
    {
        var result = new DataTable();
        using (var conn = new SqlConnection(ConnectionString))
        using (var cmd = new SqlCommand("SELECT * FROM t", conn))
        using (var sda = new SqlDataAdapter(cmd))
        {
            sda.Fill(result);
        }
        return result;
    }
}

public partial class Form1 : Form
{
    private List<UserLoginInfo> LoginInfo = new List<UserLoginInfo> {
        new UserLoginInfo() {SerialNumber = "1", Username = "Admin", Password = "123"}, 
        new UserLoginInfo() {SerialNumber = "2", UserName = "Admin2", Password = "456"}
    };

    private void button1_Click(object sender, EventArgs e)
    {
        try 
        {
            DB.SaveUserData(LoginInfo);
            dataGridView1.DataSource = DB.GetLoginData();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }    
    }
}

Answer 2

Yes that can be done very easily. I have done this in vb.net as under.. I have taken array arr with 4 columns and two rows..

            Dim Arr()() = {({1, "Document Title", "TITLE", "C"}),
                          ({2, "Company Header1", "HEADER1", "C"})}

Now for inserting this into database you can simply run insert query for inserting data in your own way... for e.g.

    for each item in arr.items
         exexuteNonQuery("INSERT INTO Supplier (SupplierID, Name, abc,xyz) VALUES (@SID, @Name, @abc, @xyz)")
    next