none
Visual Studio 2017 RRS feed

  • Question

  • want to add all the records in the data grid view to a sql server database table by clicking a button.

    i have used a separate class for the database connection and  metro ui framework.

                    

    Database Class class Database { private SqlConnection con; private SqlCommand cmd; private SqlDataAdapter da; private SqlDataReader dr; public Database() { con = new SqlConnection("Data Source=DESKTOP-GSJITB6\\MICROSOFT;Initial Catalog=NIBM;Integrated Security=True"); } public void openConnection() { con.Open(); } public void closeConnection() { con.Close(); } public int save_update_delete(string a) { openConnection(); cmd = new SqlCommand(a, con); int i = cmd.ExecuteNonQuery(); closeConnection(); return i; } } WINDOWS FORM

    Database db = new Database(); DataTable table = new DataTable(); table = (DataTable)this.metroGrid1.DataSource; foreach (DataRow item in table.Rows) { int k = db.save_update_delete("insert into ReturnDetail(RInvNo,ItemNo,RQty) values('" + + item["InvoiceNo"].ToString() + "', '" + item["ItemNo"].ToString() + "','" + item["Quantity"].ToString() + "')"); }


    but this code throws an error "Object reference not set to an instance of an object"


    Friday, May 24, 2019 5:32 PM

Answers

  • Hello,

    You should have a method that accepts a DataTable as shown below which adds all records at once with checks for null values. Also this example there is a primary key which gets assigned directly after the insert is successful.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace YourNamespace
    {
        public class DataOperations
        {
    
            private string ConnectionString = "TODO";
    
            public void InsertRecordsFromDataGridView(DataTable pDataTable)
            {
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = "INSERT INTO Players " +
                                          "(Name,Team,Position,Height,[Weight],Age) " +
                                          "VALUES (@Name,@Team,@Position,@Height,@Weight,@Age); " +
                                          "SELECT CAST(scope_identity() AS int);";
    
    
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Name", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Team", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Position", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Height", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Weight", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Age", DbType = DbType.Decimal });
    
    
                        cn.Open();
    
                        foreach (DataRow row in pDataTable.Rows)
                        {
                            cmd.Parameters["@Name"].Value = row.FieldOrDefault<string>("Name");
                            cmd.Parameters["@Team"].Value = row.FieldOrDefault<string>("Team");
                            cmd.Parameters["@Position"].Value = row.FieldOrDefault<string>("Position");
                            cmd.Parameters["@Height"].Value = row.FieldOrDefault<int>("Height");
                            cmd.Parameters["@Weight"].Value = row.FieldOrDefault<int>("Weight");
                            cmd.Parameters["@Age"].Value = row.FieldOrDefault<decimal>("Age");
    
                            row.SetField("id", Convert.ToInt32(cmd.ExecuteScalar()));
    
                        }
                    }
                }
            }
        }
    
        public static class Extensions
        {
    
            public static T FieldOrDefault<T>(this DataRow row, string columnName)
            {
                return row.IsNull(columnName) ? default(T) : row.Field<T>(columnName);
            }
    
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 24, 2019 7:02 PM
    Moderator

All replies

  • You never created he Database class;   In the windows form you need Database db = new DataBase() before calling the db.save_update_delete function.

    Friday, May 24, 2019 5:50 PM
  • Hello,

    You should have a method that accepts a DataTable as shown below which adds all records at once with checks for null values. Also this example there is a primary key which gets assigned directly after the insert is successful.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace YourNamespace
    {
        public class DataOperations
        {
    
            private string ConnectionString = "TODO";
    
            public void InsertRecordsFromDataGridView(DataTable pDataTable)
            {
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = "INSERT INTO Players " +
                                          "(Name,Team,Position,Height,[Weight],Age) " +
                                          "VALUES (@Name,@Team,@Position,@Height,@Weight,@Age); " +
                                          "SELECT CAST(scope_identity() AS int);";
    
    
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Name", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Team", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Position", DbType = DbType.String });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Height", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Weight", DbType = DbType.Int32 });
                        cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Age", DbType = DbType.Decimal });
    
    
                        cn.Open();
    
                        foreach (DataRow row in pDataTable.Rows)
                        {
                            cmd.Parameters["@Name"].Value = row.FieldOrDefault<string>("Name");
                            cmd.Parameters["@Team"].Value = row.FieldOrDefault<string>("Team");
                            cmd.Parameters["@Position"].Value = row.FieldOrDefault<string>("Position");
                            cmd.Parameters["@Height"].Value = row.FieldOrDefault<int>("Height");
                            cmd.Parameters["@Weight"].Value = row.FieldOrDefault<int>("Weight");
                            cmd.Parameters["@Age"].Value = row.FieldOrDefault<decimal>("Age");
    
                            row.SetField("id", Convert.ToInt32(cmd.ExecuteScalar()));
    
                        }
                    }
                }
            }
        }
    
        public static class Extensions
        {
    
            public static T FieldOrDefault<T>(this DataRow row, string columnName)
            {
                return row.IsNull(columnName) ? default(T) : row.Field<T>(columnName);
            }
    
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 24, 2019 7:02 PM
    Moderator