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.

There are as many ways to do this as there are programmers in the world. Make an attempt, ask for help with that attempt. Until then, I have to vote to close. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 20, 2018 at 18:08
@CodeNotFound - just had to laugh about your username "CodeNotFound" while you are writing a comment about missing code. :D — Rand Random
– Rand Random, Commented Jun 20, 2018 at 18:09
It is NEVER okay to use string concatenation to put data into an sql command. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 20, 2018 at 18:42
Your string concatenation is wide open to sql injection. It is the absolute worst possible way to get data into the database. But my friend bobby tables LOVES this type of thing. bobby-tables.com Learn how to use parameterized queries, then go back and fix ALL of your code. — Sean Lange
– Sean Lange, Commented Jun 20, 2018 at 18:48

Joel Coehoorn · Accepted Answer · 2018-06-20 19:58:27Z

3

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);
        }    
    }
}

edited Jun 20, 2018 at 19:58

answered Jun 20, 2018 at 18:15

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

CodeNotFound Over a year ago

I may add that "It is a 0-based index" :)

Khandkar Asif Hossain Over a year ago

Tried. The code compiled at least but giving weird value. The Data Grid View is not showing the first row. @Joel Coehoorn

Karthik Ganesan Over a year ago

@KhandkarAsifHossain Side Note: Your query variable is overwritten after each iteration

Joel Coehoorn Over a year ago

And PLEASE look at how I changed the code. The code in the question is just begging to get hacked. It is NEVER okay to use string concatenation to put data into an sql command.

ItamarG3 Over a year ago

@JoelCoehoorn (flying by from review queue and just happened to see this) Wow. This might be meaningless to you, but I'd like to commend you for the patience and kindness you show. Being able to both answer the question and explain about sql injection in one post is truly amazing. Just nice to see users like you here and there. :D

|

Amrita Srivastava · Accepted Answer · 2018-06-20 18:17:33Z

0

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

answered Jun 20, 2018 at 18:17

Amrita Srivastava

3841 silver badge7 bronze badges

1 Comment

CodeNotFound Over a year ago

Great answer because it provides the best practice about parametizing the query. This should be the accepted answer. Anyway the OP is using C# so you should a least convert it :)

Collectives™ on Stack Overflow

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

2 Answers 2

18 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

18 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related