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