Inserting values from DataTable into database

Question

I have store parent Gridview and child Gridview values into a Datatable. I have used a three tier architecture and want to save this values into the database but when applying the iteration using for loop at one iteration the value is integer so it takes but the other iteration the value is varchar I cannot convert into the varchar because my some values are integer and some varchar.

public string Insertrecord(DataTable dts,string CourseCode,int CourseId)
{
        DBHelper.OpenConection();
        DBHelper.BeginTransaction();

        string query = "";

        for (int i = 0; i <= dts.Rows.Count - 1; i++)
        {
            for (int j = 0; j <= dts.Columns.Count - 1; j++)
            {
                 query = ("Insert into ShortCourses values('" + CourseId + "','" + CourseCode + "','" + dts.Rows[i][j] + "')");
                 DBHelper.ExecNonQuery(query).ToString();
            }
        }

        DBHelper.CommitTransaction();
        DBHelper.CloseConnection();

        return query;
}

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Sep 8, 2019 at 10:17

Vignesh Kumar A · Accepted Answer · 2019-09-08 10:25:46Z

You should treat all value as string since you are holding those values in query which is string. Also below is not recommended to write for good code. Please always go for parametrized queries.

public string Insertrecord(DataTable dts,string CourseCode,int CourseId)
{
      DBHelper.OpenConection();
      DBHelper.BeginTransaction();
      string query = "";
      for (int i = 0; i <= dts.Rows.Count - 1; i++)
      {
          for (int j = 0; j <= dts.Columns.Count - 1; j++)
          {
               query = ("Insert into ShortCourses values('" + CourseId.ToString() + "','" + CourseCode.ToString() + "','" + Convert.ToString(dts.Rows[i][j]) + "')");
               DBHelper.ExecNonQuery(query).ToString();
          }
      }
      DBHelper.CommitTransaction();
      DBHelper.CloseConnection();
      return query;
}

parametrized queries.

 public string Insertrecord(DataTable dts,string CourseCode,int CourseId)
    {
          DBHelper.OpenConection();
          DBHelper.BeginTransaction();
          string query = "";
          for (int i = 0; i <= dts.Rows.Count - 1; i++)
          {
              for (int j = 0; j <= dts.Columns.Count - 1; j++)
              {
                  query = "Insert into ShortCourses values(@Id, @Code, @Val)";           
                  SqlCommand cmd = new SqlCommand(query, con);

            //Pass values to Parameters
                 cmd.Parameters.AddWithValue("@Id", CourseId.ToString());
                 cmd.Parameters.AddWithValue("@Code", CourseCode.ToString());
                 cmd.Parameters.AddWithValue("@Val", Convert.ToString(dts.Rows[i][j])));                   
                 cmd.ExecuteNonQuery();
          }
          DBHelper.CommitTransaction();
          DBHelper.CloseConnection();
          return query;
    }

Getting error : Column name or number of supplied values does not match table definition.

Collectives™ on Stack Overflow

Inserting values from DataTable into database

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related