2 Questions about ADO.net

T

Tom

1. I drag the sqlDataAdapter and sqlConnection from the
tool bar to the web form.

It creates automatically:

Code: 
protected System.Data.SqlClient.SqlDataAdapter
sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand
sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand
sqlInsertCommand1;
protected System.Data.SqlClient.SqlCommand
sqlUpdateCommand1;
protected System.Data.SqlClient.SqlCommand
sqlDeleteCommand1;
protected System.Data.SqlClient.SqlConnection
sqlConnection1;

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}

private void InitializeComponent()
{
this.sqlDataAdapter1 = new
System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new
System.Data.SqlClient.SqlConnection();
this.Button1.Click += new System.EventHandler
(this.Button1_Click);

this.sqlDataAdapter1.DeleteCommand =
this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand =
this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand =
this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping
("Table", "phonebook", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping
("id", "id"),
new System.Data.Common.DataColumnMapping
("name", "name"),
new System.Data.Common.DataColumnMapping
("phone", "phone"),

this.sqlDataAdapter1.UpdateCommand =
this.sqlUpdateCommand1;

this.sqlSelectCommand1.CommandText = "SELECT id,
name, phone FROM dbo.phonebook";
this.sqlSelectCommand1.Connection =
this.sqlConnection1;

this.sqlInsertCommand1.CommandText = "INSERT INTO
dbo.phonebook(name, phone) VALUES (@name, @phone); SELECT
id, name, phone FROM dbo.phonebook WHERE (id =
@@IDENTITY)";

this.sqlInsertCommand1.Connection =
this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.CommandText = @"UPDATE
dbo.phonebook SET name = @name, phone = @phone WHERE (id =
@Original_id) AND (phone = @Original_phone); SELECT id,
name, phone FROM dbo.phonebook WHERE (id = @id)";

this.sqlUpdateCommand1.Connection =
this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id",
System.Data.SqlDbType.Int, 4, "id"));


this.sqlDeleteCommand1.CommandText = "DELETE FROM
dbo.phonebook WHERE (id = @Original_id) AND (phone =
@Original_phone)";
this.sqlDeleteCommand1.Connection =
this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";
this.Load += new System.EventHandler
(this.Page_Load);
}

private void Button1_Click(object sender, System.EventArgs
e)
{
}

Although insert, delete, update and select sql statements
were done automatically, however, how can i make use of
them? i.e. how can I call them to use under Button1_Click?

2. You can see the this.sqlConnection1.ConnectionString
= "data source=SERVER;initial catalog=phonedb;persist
security info=False;user
id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

It has to include the login id and password in order to
make such connection. I guess it is not secure. How should
I write a secure dbconnection code? Am I better to write a
new DBConnect class?

If I use my DBConnect class, does the above auto-generated
code not useful?

Thanks for advice.
 
N

Nicholas Paldino [.NET/C# MVP]

Tom,

In order to call the data adatper in your click event handler, you can
just call to sqlDataAdapter1. It's a member of your class, like any other
member. So, if you wanted to fill a dataset with the results from the
query, you would do:

// Fill the data set.
DataSet pobjDataSet = new DataSet();
sqlDataAdapter1.Fill(pobjDataSet);

As for creating a new connection class, that's not necessary. You have
one of two options here. The first is to store the connection information
in another location which is more secure. The second is to use trusted
connections (windows security) and not have to worry about logging in at
all.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Tom said:
1. I drag the sqlDataAdapter and sqlConnection from the
tool bar to the web form.

It creates automatically:

Code: 
protected System.Data.SqlClient.SqlDataAdapter
sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand
sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand
sqlInsertCommand1;
protected System.Data.SqlClient.SqlCommand
sqlUpdateCommand1;
protected System.Data.SqlClient.SqlCommand
sqlDeleteCommand1;
protected System.Data.SqlClient.SqlConnection
sqlConnection1;

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}

private void InitializeComponent()
{
this.sqlDataAdapter1 = new
System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new
System.Data.SqlClient.SqlConnection();
this.Button1.Click += new System.EventHandler
(this.Button1_Click);

this.sqlDataAdapter1.DeleteCommand =
this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand =
this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand =
this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping
("Table", "phonebook", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping
("id", "id"),
new System.Data.Common.DataColumnMapping
("name", "name"),
new System.Data.Common.DataColumnMapping
("phone", "phone"),

this.sqlDataAdapter1.UpdateCommand =
this.sqlUpdateCommand1;

this.sqlSelectCommand1.CommandText = "SELECT id,
name, phone FROM dbo.phonebook";
this.sqlSelectCommand1.Connection =
this.sqlConnection1;

this.sqlInsertCommand1.CommandText = "INSERT INTO
dbo.phonebook(name, phone) VALUES (@name, @phone); SELECT
id, name, phone FROM dbo.phonebook WHERE (id =
@@IDENTITY)";

this.sqlInsertCommand1.Connection =
this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.CommandText = @"UPDATE
dbo.phonebook SET name = @name, phone = @phone WHERE (id =
@Original_id) AND (phone = @Original_phone); SELECT id,
name, phone FROM dbo.phonebook WHERE (id = @id)";

this.sqlUpdateCommand1.Connection =
this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id",
System.Data.SqlDbType.Int, 4, "id"));


this.sqlDeleteCommand1.CommandText = "DELETE FROM
dbo.phonebook WHERE (id = @Original_id) AND (phone =
@Original_phone)";
this.sqlDeleteCommand1.Connection =
this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";
this.Load += new System.EventHandler
(this.Page_Load);
}

private void Button1_Click(object sender, System.EventArgs
e)
{
}

Although insert, delete, update and select sql statements
were done automatically, however, how can i make use of
them? i.e. how can I call them to use under Button1_Click?

2. You can see the this.sqlConnection1.ConnectionString
= "data source=SERVER;initial catalog=phonedb;persist
security info=False;user
id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

It has to include the login id and password in order to
make such connection. I guess it is not secure. How should
I write a secure dbconnection code? Am I better to write a
new DBConnect class?

If I use my DBConnect class, does the above auto-generated
code not useful?

Thanks for advice.
Click to expand...
 
T

Tom

Dear Sir,

Thank you for your kind help. Since I am a newbie and just
read some ASP.net and C# books. I am not still familiar
with it.

Here are my questions:

1. Do I write the code like that?

private void Button1_Click(object sender, System.EventArgs
e)
{
DataSet pobjDataSet = new DataSet();
sqlDataAdapter1.Fill(pobjDataSet);
}

But, how can I call the insert command? I need to input
the textbox to the DB.

2. The first is to store the connection information in
another location which is more secure.

Do you mean store it in the registry? How can I call it
from the code after I save it in registry?

The second is to use trusted connections (windows
security) and not have to worry about logging in at
all.

If I use windows security (I guess it is better), how
should I write the following?

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

Regards

Tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Newbie Update, Insert, & Delete Using Visual Studio And TextBoxes 2
Issue with Databinding. 11
Code 4
Updating Database using a Stored procedure 3
Insert into Access with C# 1
Stored Procedure Parameters 1
urgent !!!! problem updating data set 3
SqlDataAdapter1.update for datagrid1 does not work 1

Top