Storing the url in SQLSERVER 2005 using C# code(data sets)

Question

I was trying to store the url on button click using the following code.There were no error but the required url is not sroeing in my column field (i used ntext data tpe for this).Please help me if there was some mistake in my code

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    public void Storetxt(String txt)
    {


        //connection to the database

        string connection = "Data Source=.\\sqlexpress2005;Initial Catalog=PtsKuratlas;Integrated Security=True";
        SqlConnection conn = new SqlConnection(connection);

       //dataset object to store and manipulating data

        DataSet myDataSet = new DataSet();

        //data adapters to execute SQL

        SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT * FROM gti_analytics", conn);
        myDataAdapter.Fill(myDataSet, "gti_analytics");
        myDataAdapter.InsertCommand = new SqlCommand("INSERT INTO gti_analytics [links] VALUES [txt]");




    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        String text = "http://google.com";
        Storetxt(text);
    }
}

Answer 1

shouldn't you be calling myDataAdapter.Update() at the end of the Storetxt method? oh and i think using ntext for this is overkill.

Answer 2

The problem is that you're not actually executing the command against the database. You're defining the InsertCommand to use, but it's not being executed.

Based on that code, I don't see that you need to use a DataAdapter/DataSet anyway, just use an SqlCommand to do the insert, which is more lightweight. Something like this:

public void Storetxt(String txt)
{
    //connection to the database
    string connection = "Data Source=.\\sqlexpress2005;Initial Catalog=PtsKuratlas;Integrated Security=True";
    SqlConnection conn = null;
    SqlCommand cmd = null;
    try
    {
        conn = new SqlConnection(connection);
        cmd = new SqlCommand("INSERT INTO gti_analytics (Links) VALUES (@Link)", conn);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Link", txt);
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    catch{//handle exceptions}
    finally
    {
        if (cmd != null) cmd.Dispose();
        if (conn != null) 
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
        }
    }
}

I'd also recommend not using ntext for this in your db. If you really need unicode support, use nvarchar which can go up to 4000 chars pre-sql 2005, or nvarchar(max) which can store as much as ntext from SQL 2005 onwards. If you don't need unicode support, use varchar instead (8000 chars pre-sql 2005, VARCHAR(MAX) from SQL 2005 onwards allows same as text)

Answer 3

Your txt method argument is not actually used anywhere in your method which is one reason why it's not being stored in the db.

Answer 4

You don't need a SqlDataAdapter or a DataSet for this operation, as AdaTheDev said. Follow the sample code, although it needs quite a bit of cleaning up.

Also, there's a protocol limit on the number of characters/bytes in the URL, nvarchar should have enough maximum capacity, so you should need neither nvarchar(max) nor (pre-SQL Server 2005) ntext.