2 Questions about ADO.net

[Tom]

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

It creates automatically:

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.sqlConnect ion1.Connection String
= "data source=SERVER;i nitial catalog=phonedb ;persist
security info=False;user
id=phoneuser;pa ssword:useruser ;workstation
id=SERVER;packe t 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.

[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(pobjDataS et);

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]
- mv*@spam.guard. caspershouse.co m

"Tom" <ke*****@yahoo. com> wrote in message
news:09******** *************** *****@phx.gbl.. .

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

It creates automatically:

 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.sqlConnect ion1.Connection String
= "data source=SERVER;i nitial catalog=phonedb ;persist
security info=False;user
id=phoneuser;pa ssword:useruser ;workstation
id=SERVER;packe t 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.

[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(o bject sender, System.EventArg s
e)
{
DataSet pobjDataSet = new DataSet();
sqlDataAdapter1 .Fill(pobjDataS et);
}

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.sqlConnect ion1.Connection String = "data
source=SERVER;i nitial catalog=phonedb ;persist security
info=False;user id=phoneuser;pa ssword:useruser ;workstation
id=SERVER;packe t size=4096";

Regards

Tom

-----Original Message-----
Tom,

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

// Fill the data set.
DataSet pobjDataSet = new DataSet();
sqlDataAdapter 1.Fill(pobjData Set);

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

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m