DataSet help in C#

Question

Hello, I connected an sql database in c# and now trying to put the contents into a dataset. How will I be able to do that?

My code is:

string constr = "Data Source=ECEE;Initial Catalog=Internet_Bankaciligi;User ID=sa";

        SqlConnection conn = new SqlConnection(constr);

        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Internet_Bankaciligi", conn);
        DataSet myDataSet = new DataSet();
        DataRow myDataRow;


        SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);


        mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

        mySqlDataAdapter.Fill(myDataSet,"Internet_Bankaciligi");

        myDataRow = myDataSet.Tables["IB_Account"].NewRow();
        myDataRow["Account_ID"] = "NewID";
        myDataRow["Branch_ID"] = "New Branch";
        myDataRow["Amount"] = "New Amount";

        myDataSet.Tables["Customers"].Rows.Add(myDataRow);

the line: "mySqlDataAdapter.Fill(myDataSet,"Internet_Bankaciligi");" gives an error as 'Invalid object name 'Internet_Bankaciligi'.' but Internet_Bankaciligi is my database name.

Also if i use:

SqlCommand selectCMD = new SqlCommand("select (*) from IB_Account", conn);

        SqlDataAdapter myAdapter = new SqlDataAdapter();
        myAdapter.SelectCommand = selectCMD;

        myAdapter.Fill(myDataSet);

then: "SqlCommand selectCMD = new SqlCommand("select (*) from IB_Account", conn);" gives an error saying invalid syntax. How will I get it correct?

Answer 1

If "Internet_Bankaciligi" is your actual database name, then you can't execute a SQL command directly against it. You have to change your SQL to select from a table or a view.

Your second example doesn't work because "SELECT (*)" is not valid syntax. It should be "SELECT * FROM IB_Account"... no parentheses.

Answer 2

I checked this statement in Sql Server 2008:

Select (*) from <table>

It doesn't work. I never seen this syntax, not in sqlserver 2005, nor Oracle nor sqlite.

try this one:

Select * from <table>

---

Edit: If I were you I will try using strongly typed datasets, or even Entity Framework which both are more advance and easier to work with. Google them.

Answer 3

You'll need to use the System.Data.SqlClient namespace and its classes, namely: SqlConnection SqlCommand and SqlDataReader

Check this article HOW TO: SQL & C# for details on how to connect to SQL Server database from C#.NET database applications as well as Java database applications. It also describes how to pass embedded SQL queries, calling stored procedures, pass parameters etc.