Using System;
Using System.Collections.Generic;
Using System.Text;
Using Console = System.Console;
Using Microsoft.Data.Odbc;
Using System.Data;
Using System.Data.SqlClient;
Namespace ConsoleApplication1
{
Class Program
{
static void Main (string[] args)
{
Try
{
Connsqlserver ();
}
catch (OdbcException myodbcexception)
{
for (int i = 0; i < MyOdbcException.Errors.Count; i++)
{
Console.Write ("ERROR #" + i + "\ n" +
"Message:" + myodbcexception.errors[i]. Message + "\ n" +
"Native:" + myodbcexception.errors[i]. Nativeerror.tostring () + "\ n" +
"Source:" + myodbcexception.errors[i]. Source + "\ n" +
"SQL:" + myodbcexception.errors[i]. SQLState + "\ n");
}
}
Console.ReadLine ();
}
public void Ceshi ()
{
Try
{
String myconstring = "Driver={mysql ODBC 3.51 DRIVER};" +
"Server=localhost;" +
"DATABASE=JY;" +
"Uid=root;" +
"PASSWORD=123;" +
"OPTION=3; charset=gb2312; ";
OdbcConnection myconnection = new OdbcConnection (myconstring);
Myconnection.open ();
Console.WriteLine ("\ n!!! Success, connected successfully!!! \ n ");
Create a sample Table
OdbcCommand mycommand = New OdbcCommand ("DROP TABLE IF EXISTS my_odbc_net", MyConnection);
Mycommand.executenonquery ();
myCommand.CommandText = "CREATE TABLE my_odbc_net (id int, name varchar (), IDB bigint)";
Mycommand.executenonquery ();
Insert
myCommand.CommandText = "INSERT into my_odbc_net VALUES" + Filter ("China," ") +" ', 300) ";
Console.WriteLine ("INSERT, total rows affected:" + mycommand.executenonquery ());;
Insert
myCommand.CommandText = "INSERT into my_odbc_net VALUES (' MySQL ', 400)";
Console.WriteLine ("INSERT, total rows affected:" + mycommand.executenonquery ());
Insert
myCommand.CommandText = "INSERT into my_odbc_net VALUES (' MySQL ', 500)";
Console.WriteLine ("INSERT, total rows affected:" + mycommand.executenonquery ());
Update
myCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20";
Console.WriteLine ("Update, total rows affected:" + mycommand.executenonquery ());
COUNT (*)
myCommand.CommandText = "Select COUNT (*) as trows from My_odbc_net";
Console.WriteLine ("Total Rows:" + mycommand.executescalar ());
Fetch
myCommand.CommandText = "SELECT * from My_odbc_net";
OdbcDataReader MyDataReader;
MyDataReader = Mycommand.executereader ();
while (Mydatareader.read ())
{
if (String.Compare (Myconnection.driver, "myodbc3.dll") = = = 0)
{
Console.WriteLine ("Data:" + mydatareader.getint32 (0) + "" +
Mydatareader.getstring (1) + "" +
Mydatareader.getint64 (2)); Supported only by CONNECTOR/ODBC 3.51
}
Else
{
Console.WriteLine ("Data:" + mydatareader.getint32 (0) + "" +
Mydatareader.getstring (1) + "" +
Mydatareader.getint32 (2)); Bigints not supported by CONNECTOR/ODBC
}
}
Close All Resources
Mydatareader.close ();
Myconnection.close ();
}
catch (OdbcException myodbcexception)//catch any ODBC exception:
{
for (int i = 0; i < MyOdbcException.Errors.Count; i++)
{
Console.Write ("ERROR #" + i + "\ n" +
"Message:" + myodbcexception.errors[i]. Message + "\ n" +
"Native:" + myodbcexception.errors[i]. Nativeerror.tostring () + "\ n" +
"Source:" + myodbcexception.errors[i]. Source + "\ n" +
"SQL:" + myodbcexception.errors[i]. SQLState + "\ n");
}
}
}
public static void Connsqlserver ()
{
String myconstring = "Driver={mysql ODBC 3.51 DRIVER};" +
"Server=localhost;" +
"DATABASE=ZS;" +
"Uid=root;" +
"PASSWORD=123;" +
"OPTION=3; charset=gb2312; ";
OdbcConnection myconnection = new OdbcConnection (myconstring);
Myconnection.open ();
OdbcCommand mycommand = New OdbcCommand ("DROP TABLE IF EXISTS my_odbc_net", MyConnection);
Mycommand.executenonquery ();
Display data using a lightweight SqlDataReader
Specify the connection string for the SQL Server provider
String connstring = "Data source=pc-200908231053\\sqlexpress;database=occupationnew; User Id=sa; Pwd=123 ";
Establish a Connection object
SqlConnection sqlconn = new SqlConnection (connstring);
Open connection
Sqlconn.open ();
String Thiscommand = "SELECT * from article the WHERE ID >" + 1243931905062 + "ORDER by ID";
Create a SqlDataAdapter object with two parameters, one query string and one Connection object
SqlDataAdapter sqldap = new SqlDataAdapter (Thiscommand, sqlconn);
Create a DataSet object
DataSet Thisdataset = new DataSet ();
Populate the DataSet with the Fill method of the SqlDataAdapter, with two parameters, one created dataset instance, and one filled-in table
Sqldap.fill (Thisdataset, "informations");
Show Query Results
foreach (DataRow therow in thisdataset.tables["informations"]. Rows)
{
Console.WriteLine (therow["Informationid"] + "\ T" + therow["CompanyName"]);
myCommand.CommandText = "INSERT into information (companyname,course,major,number,sex,pay,request, Informationsource,datetime,job,workprovinci,detailplace,isbin,enddate,existcourse,academymajororder) "
+ "VALUES ('" + Filter (therow["CompanyName"]) + "', '" + Filter (therow["Course"]) + "', '" + Filter (therow["Major"]) + "', '" + Filter (therow["number"]) + "', '" + Filter (therow["Sex"]) + "', '" + Filter (therow["pay"]) + "', '" + Filter (therow[" Request "]) +" ', ' "+ Filter (therow[" Informationsource "]) +" ', ' "+ Filter (therow[" DateTime "]) +" ', ' "+ Filter (therow[" Job "]) +" ', ' "+ Filter (therow[" Workprovince "]) +" ', ' "+ Filter (therow[" Detailplace "]) +" ', ' "+ Filter (therow[" IsBin "]) +" ', ' + filter (therow["EndDate"]) + "', '" + Filter (therow["Existcourse"]) + "', '" + Filter (therow["Academymajororder"]) + " ‘)";
Console.WriteLine (therow["ID"] + "\ T" + therow["Title"]);
myCommand.CommandText = "INSERT into downloads (Title,fenlei,content,lint,updatetime,click)"
+ "VALUES" + filter (therow["Title") + "', '" + Filter (therow["CategoryID"]) + "', '" + Filter (therow["Content"]) + "', '" + Filter (therow["Author"]) + "', '" + Filter (therow["DateTime"]) + "', '" + Filter (therow["Hits"]) + "')";
Console.WriteLine (therow["ID"] + "\ T" + therow["Title"]);
myCommand.CommandText = "INSERT into Xinwens (Title,fenlei,content,username,updatetime,click)"
+ "VALUES" + filter (therow["Title"]) + "', '" + "+" ', ' "+ Filter (therow[" Content "]) +" ', ' "+ Filter (" University Student Employment Guidance Center ") + "', '" + Filter (therow["DateTime"]) + "', '" + 0 + "')";
Console.WriteLine ("INSERT, total rows affected:" + mycommand.executenonquery ());
}
Sqlconn.close ();
Console.ReadLine ();
}
public static string Filter (object text)
{
System.Text.Encoding GB2312 = System.Text.Encoding.GetEncoding ("GB2312");
System.Text.Encoding UTF8 = System.Text.Encoding.UTF8;
byte[] data = GB2312. GetBytes (text. ToString ());
String msg = GB2312. GetString (data);
Return MSG. Replace (",", ","). Replace ("'", "'"). Replace ("―", "-");
}
}
}
C # Read SQL Server data added to MySQL database