Inserting data into a server database

[Nishara]

Hi..

I want to create a seperate DataAccessLayer and do the inserting,updating...etc to a database.Actually I'm new to .net things.
I did it as below.But the database is not updated at all..

This is the DataAccessLayer class...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClassTest
{
    class DataAccessLayer
    {
        public SqlConnection cn;
        public SqlCommand dataCmd;
        public SqlDataAdapter da;
        public DataTable dt;

        public String fullsql;

        public DataAccessLayer()
        {

        }

        public DataTable getTable(String sql)
        {
            cn = new SqlConnection("Data Source=MICROSOFTSL-PC;Initial Catalog=Test;User ID=sa;Password=sa");
            dataCmd = new SqlCommand();

            cn.Open();
            dt = new DataTable();
            dataCmd.CommandText = sql;
            da = new SqlDataAdapter(dataCmd);
            da.SelectCommand = new SqlCommand(sql, cn);      
            da.Fill(dt);          
            cn.Close();

            return dt;
        }

        public void updateTable(DataTable table)
        {
            try
            {
                getTable(fullsql);
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                table = dt;              
                da.Update(table);               
            }
            catch (Exception ex)
            {
                String error=ex.Message;
            }
        }
       
    }
}

This is the form1.cs where I want to instantiate the DAL..

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClassTest
{
    class DataAccessLayer
    {
        public SqlConnection cn;
        public SqlCommand dataCmd;
        public SqlDataAdapter da;
        public DataTable dt;

        public String fullsql;

        public DataAccessLayer()
        {

        }

        public DataTable getTable(String sql)
        {
            cn = new SqlConnection("Data Source=MICROSOFTSL-PC;Initial Catalog=Test;User ID=sa;Password=sa");
            dataCmd = new SqlCommand();

            cn.Open();
            dt = new DataTable();
            dataCmd.CommandText = sql;
            da = new SqlDataAdapter(dataCmd);
            da.SelectCommand = new SqlCommand(sql, cn);      
            da.Fill(dt);          
            cn.Close();

            return dt;
        }

        public void updateTable(DataTable table)
        {
            try
            {
                getTable(fullsql);
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                table = dt;              
                da.Update(table);               
            }
            catch (Exception ex)
            {
                String error=ex.Message;
            }
        }
       
    }
}

I can't think of why this doesn't store the values..
Plz help me.
Thank you so much..

[sknake]

You need to specify Insert, Update, Delete, and Select queries for your data adapter.

[Nishara]

You need to specify Insert, Update, Delete, and Select queries for your data adapter.

Dear sknake,

I'm really sorry for the mistake.I hadn't attached the form1.cs file.I have specified the select query there..But data is not stored in the DB.

Form1.cs

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

namespace ClassTest
{
    public partial class Form1 : Form
    {
        private SqlConnection cn;
        private SqlCommand dataCmd;
        
        public string fullsql;

       
        private DataAccessLayer dal;


        public Form1()
        {
            InitializeComponent();

            cn = new SqlConnection("Data Source=MICROSOFTSL-PC;Initial Catalog=Test;User ID=sa;Password=sa");
            dataCmd = new SqlCommand();

        }

        DataTable UserTable = new DataTable();


        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'testDataSet.Users' table. You can move, or remove it, as needed.
            this.usersTableAdapter1.Fill(this.testDataSet.Users);
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {   
                fullsql = "Select * from Users";               
                dal = new DataAccessLayer();
                UserTable = dal.getTable(fullsql);
               
                DataRow dtr = UserTable.NewRow();

                dtr["UserID"] = txtID.Text;
                dtr["User_Name"] = txtName.Text;

                UserTable.Rows.Add(dtr);
                MessageBox.Show("Row Inserted");
                this.usersTableAdapter1.Fill(this.testDataSet.Users);
               
                dal.updateTable(UserTable);
                UserTable = dal.getTable(fullsql);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

Thanks.

[sknake]

Upload a sample project demonstrating this behavior and i'll take a look at it.