private void populateComboes() { TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder(); stringBuilder.CommandTimeout = 300; stringBuilder.ConnectionTimeout = 100; stringBuilder.DataSource = host; stringBuilder.UserId = uid; stringBuilder.Password = pwd; using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) { dbConnection.Open(); TdDataAdapter adapter = new TdDataAdapter(); DataTable dt = new DataTable(); TdCommand myCommand = new TdCommand("select databasename from dbc.databases", dbConnection); TdDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { cboDatabaseList.Items.Add(myReader[0].ToString().Trim()); } myReader.Close(); myCommand.Dispose(); } }
private void cboDatabaseList_TextChanged(object sender, EventArgs e) { TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder(); stringBuilder.CommandTimeout = 300; stringBuilder.ConnectionTimeout = 100; stringBuilder.DataSource = host; stringBuilder.UserId = uid; stringBuilder.Password = pwd; using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) { dbConnection.Open(); TdDataAdapter adapter = new TdDataAdapter(); DataTable dt = new DataTable(); TdCommand myCommand = new TdCommand("select tablename from dbc.TablesVX where databasename='" + cboDatabaseList.Text + "'", dbConnection); TdDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { cboTables.Items.Add(myReader[0].ToString().Trim()); } myReader.Close(); myCommand.Dispose(); } }
public Teradata(String user, String pwd) { connectionStringBuilder = new TdConnectionStringBuilder(); connectionStringBuilder.AuthenticationMechanism = "LDAP"; connectionStringBuilder.UserId = user; connectionStringBuilder.Password = pwd; connectionStringBuilder.DataSource = "10.102.5.28"; cn = new TdConnection(); }
static void Main(string[] args) { TdConnection cn = new TdConnection(); TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder(); conStrBuilder.DataSource = "DSN"; // conStrBuilder.Database = "optional"; conStrBuilder.UserId = "user"; conStrBuilder.Password = "******"; cn.ConnectionString = conStrBuilder.ConnectionString; cn.Open(); Console.WriteLine("connection successfull"); }
public string BuildConnectionString(string dbType, string dsnName, string auth_type, string server, string port, string database, string loginName, string loginpassword, string userDefinedDriverName, UseDriver useDriverType) { string ret = string.Empty; this.Server = server; if (dsnName != string.Empty) { // I wish to connect through named DSN, ODBC only useDriverType = UseDriver.ODBC; } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.ORACLE) { // built in native support for oracle and teradata if (dbType == "oracle") { if (string.IsNullOrEmpty(port)) { port = "1521"; } this.MyDriver = DBExecutor.UseDriver.ORACLE; this.ConnectString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={4})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={3})));User Id = {1}; Password = {2}; ", server, loginName, loginpassword, database, port); return(this.ConnectString); } } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.TERADATA) { // teradata - we have own driver if (dbType == "teradata") { this.MyDriver = DBExecutor.UseDriver.TERADATA; TdConnectionStringBuilder builder = new TdConnectionStringBuilder(); builder.SessionCharacterSet = "UTF8"; builder.DataSource = server; builder.UserId = loginName; builder.Password = loginpassword; this.ConnectString = builder.ToString(); return(this.ConnectString); } } // the only solution is find an ODBC driver if (string.IsNullOrEmpty(dsnName)) { // I wish to go through driver string driverName = string.Empty; if (userDefinedDriverName == string.Empty) { List <string> drivers = ODBCUtils.GetSystemDriverList(); switch (dbType) { case "oracle": driverName = drivers.Where(x => x.IndexOf("Oracle") >= 0).FirstOrDefault(); break; case "mssql": default: driverName = drivers.Where(x => x.IndexOf("SQL Server") >= 0).FirstOrDefault(); break; } } else { driverName = userDefinedDriverName; } if (string.IsNullOrEmpty(driverName)) { throw new Exception("No ODBC driver found, please install and try again"); } else { ret += "Driver={" + driverName + "};"; } if (string.IsNullOrEmpty(port)) { ret += "Server=" + server + ";"; } else { ret += "Server=" + server + ":" + port + ";"; } if (!string.IsNullOrEmpty(database)) { ret += "Database=" + database + ";"; } } else { // I wish to go through named DSN ret = "DSN=" + dsnName + ";"; } // add properties both for DSN or driver switch (auth_type) { case "win_auth": ret += "Authentication=Windows Authentication;"; break; case "sql_auth": ret += "UID=" + loginName + ";"; ret += "PWD=" + loginpassword + ";"; break; case "dsn_auth": default: break; } this.MyDriver = DBExecutor.UseDriver.ODBC; this.ConnectString = ret; return(ret); }
private void button1_Click(object sender, EventArgs e) { Stopwatch sw = new Stopwatch(); sw.Start(); Teradata.Client.Provider.TdConnection mainConn = new Teradata.Client.Provider.TdConnection(); TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder(); stringBuilder.CommandTimeout = 300; stringBuilder.ConnectionTimeout = 100; stringBuilder.DataSource = host; stringBuilder.UserId = uid; stringBuilder.Password = pwd; mainConn.ConnectionString = stringBuilder.ConnectionString; this.Text = host; mainConn.Open(); StreamReader gperead = new StreamReader(@txtFile.Text); string szLine = ""; string[] szFields; string[] szHeader; szLine = gperead.ReadLine(); szLine = szLine.Replace("\"", ""); szHeader = szLine.Split(SEPARATOR); Teradata.Client.Provider.TdCommand gpeCmd = new Teradata.Client.Provider.TdCommand("SELECT * FROM " + cboDatabaseList.Text + "." + cboTables.Text, mainConn); gpeCmd.CommandTimeout = 10000; int counter = 0; Teradata.Client.Provider.TdDataAdapter gpeAdapter = new Teradata.Client.Provider.TdDataAdapter(gpeCmd); gpeAdapter.UpdateBatchSize = 100000; gpeAdapter.KeepCommandBatchSequence = false; Teradata.Client.Provider.TdCommandBuilder cb = new Teradata.Client.Provider.TdCommandBuilder(gpeAdapter); DataTable dt = new DataTable(); gpeAdapter.Fill(dt); while ((szLine = gperead.ReadLine()) != null) { szLine = szLine.Replace("\"", ""); szFields = szLine.Split(SEPARATOR); DataRow dr = dt.NewRow(); if (szFields.GetUpperBound(0) == szHeader.GetUpperBound(0)) { for (int i = 0; i < szHeader.GetLength(0); i++) { if (szFields[i] == "?") { dr[szHeader[i]] = DBNull.Value; } else if (dr.Table.Columns[szHeader[i]].DataType == typeof(DateTime)) { try { dr[szHeader[i]] = Convert.ToDateTime(szFields[i].Trim('\"')); } catch (Exception ex) { dr[szHeader[i]] = DBNull.Value; } } else if (dr.Table.Columns[szHeader[i]].DataType == typeof(double)) { dr[szHeader[i]] = Convert.ToDecimal(szFields[i].Trim('\"').Replace('.', ',')); } else { try { dr[szHeader[i]] = szFields[i].Trim('\"'); } catch (Exception ex) { dr[szHeader[i]] = DBNull.Value; } } } dt.Rows.Add(dr); } if ((counter++ % 100000) == 0) { try { gpeAdapter.Update(dt); } catch (Exception ex) { MessageBox.Show(this, ex.Message); } textBox1.Text = (counter - 1).ToString(); } Application.DoEvents(); } gpeAdapter.Update(dt); textBox1.Text = counter.ToString(); dt.Dispose(); gpeCmd.Dispose(); mainConn.Close(); mainConn.Dispose(); gperead.Close(); sw.Stop(); MessageBox.Show("Done!\n" + sw.Elapsed); }
public string BuildConnectionString(string dbType, string auth_type, string server, string port, string database, string loginName, string loginpassword) { string ret = string.Empty; this.Server = server; // native support if (dbType == "oracle") { if (string.IsNullOrEmpty(port)) { port = "1521"; } this.MyDriver = DBExecutor.UseDriver.OLEDB; this.ConnectString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={4})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={3})));User Id = {1}; Password = {2}; ", server, loginName, loginpassword, database, port); return this.ConnectString; } // teradata - we have own driver if (dbType == "teradata") { this.MyDriver = DBExecutor.UseDriver.TERADATA; TdConnectionStringBuilder builder = new TdConnectionStringBuilder(); builder.SessionCharacterSet = "UTF8"; builder.DataSource = server; builder.UserId = loginName; builder.Password = loginpassword; this.ConnectString = builder.ToString(); return this.ConnectString; } // others List<string> drivers = ODBCUtils.GetSystemDriverList(); string driverName = string.Empty; switch (dbType) { case "oracle": { driverName = drivers.Where(x => x.IndexOf("Oracle") >= 0).FirstOrDefault(); break; } case "mssql": default: { driverName = drivers.Where(x => x.IndexOf("SQL Server") >= 0).FirstOrDefault(); break; } } if (string.IsNullOrEmpty(driverName)) { throw new Exception("No ODBC driver found, please install and try again"); } else { ret += "Driver={" + driverName + "};"; } if (string.IsNullOrEmpty(port)) { ret += "Server=" + server + ";"; } else { ret += "Server=" + server + ":" + port + ";"; } ret += "Database=" + database + ";"; switch (auth_type) { case "win_auth": { ret += "Authentication=Windows Authentication;"; break; } default: case "sql_auth": { ret += "UID=" + loginName + ";"; ret += "PWD=" + loginpassword + ";"; break; } } this.MyDriver = DBExecutor.UseDriver.ODBC; this.ConnectString = ret; return ret; }
public TeradataDbServiceTests() { _tdConnectionStringBuilder = new TdConnectionStringBuilder(); }
public string BuildConnectionString(Arguments args, UseDriver useDriverType) { Logger.Log("Creating connection string for " + args.dbType); string ret = string.Empty; this.Server = args.server; if (args.dsnName != string.Empty) { // I wish to connect through named DSN, ODBC only useDriverType = UseDriver.ODBC; } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.ORACLE) { // built in native support for oracle and teradata if (args.dbType == "oracle") { if (string.IsNullOrEmpty(args.port)) { args.port = "1521"; } this.MyDriver = DBExecutor.UseDriver.ORACLE; this.ConnectString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={4})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={3})));User Id = {1}; Password = {2}; ", args.server, args.loginName, args.loginpassword, args.database, args.port); return(this.ConnectString); } } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.TERADATA) { // teradata - we have own driver if (args.dbType == "teradata") { this.MyDriver = DBExecutor.UseDriver.TERADATA; TdConnectionStringBuilder builder = new TdConnectionStringBuilder(); builder.SessionCharacterSet = "UTF8"; builder.DataSource = args.server; builder.UserId = args.loginName; builder.Password = args.loginpassword; this.ConnectString = builder.ToString(); return(this.ConnectString); } } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.POSTGRESQL) { // greenplum, redhift - we have own driver if (args.dbType == "greenplum" || args.dbType == "redshift" || args.dbType == "postgres") { this.MyDriver = DBExecutor.UseDriver.POSTGRESQL; NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder(); builder.Host = args.server; builder.Encoding = "UTF8"; if (!string.IsNullOrEmpty(args.database)) { builder.Database = args.database; } if (!string.IsNullOrEmpty(args.port)) { if (int.TryParse(args.port, out int portNum) == false) { throw new ArgumentException("Could not parse port number"); } else { builder.Port = portNum; } } switch (args.auth_type) { case "win_auth": builder.IntegratedSecurity = true; builder.Username = args.loginName; Logger.Log("Using Integrated Security: username may be required."); Logger.Log("Connection string: " + builder.ToString()); break; case "sql_auth": // logging builder.Username = "******"; builder.Password = "******"; Logger.Log("Using passw"); Logger.Log("Connection string: " + builder.ToString()); builder.Password = args.loginpassword; builder.Username = args.loginName; break; case "dsn_auth": default: break; } this.ConnectString = builder.ToString(); return(this.ConnectString); } } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.SNOWFLAKE) { // snowflake - we have own driver if (args.dbType == "snowflake") { this.MyDriver = DBExecutor.UseDriver.SNOWFLAKE; if (String.IsNullOrEmpty(args.loginName) || String.IsNullOrEmpty(args.loginpassword) || String.IsNullOrEmpty(args.account)) { throw new ArgumentException("Login, password and client has to be filled in!"); } ConnectString = String.Format("account={0};user={1};password={2}", args.account, args.loginName, args.loginpassword); ConnectString += !String.IsNullOrEmpty(args.database) ? String.Format(";db={0}", args.database) : ""; ConnectString += !String.IsNullOrEmpty(args.server) ? String.Format(";host={0}", args.server) : ""; ConnectString += !String.IsNullOrEmpty(args.role) ? String.Format(";role={0}", args.role) : ""; ConnectString += !String.IsNullOrEmpty(args.warehouse) ? String.Format(";warehouse={0}", args.warehouse) : ""; Regex rgx = new Regex("password=[^;]*"); Logger.Log(String.Format("Connection string: {0}", rgx.Replace(ConnectString, "password=*****"))); return(this.ConnectString); } } // the only solution is find an ODBC driver if (string.IsNullOrEmpty(args.dsnName)) { // I wish to go through driver string driverName = string.Empty; if (args.driverName == string.Empty) { List <string> drivers = ODBCUtils.GetSystemDriverList(); switch (args.dbType) { case "oracle": driverName = drivers.Where(x => x.IndexOf("Oracle") >= 0).FirstOrDefault(); break; case "mssql": default: driverName = drivers.Where(x => x.IndexOf("SQL Server") >= 0).FirstOrDefault(); break; } } else { driverName = args.driverName; } if (string.IsNullOrEmpty(driverName)) { throw new Exception("No ODBC driver found, please install and try again"); } else { ret += "Driver={" + driverName + "};"; } if (string.IsNullOrEmpty(args.port)) { ret += "Server=" + args.server + ";"; } else { ret += "Server=" + args.server + "," + args.port + ";"; } if (!string.IsNullOrEmpty(args.database)) { ret += "Database=" + args.database + ";"; } } else { // I wish to go through named DSN ret = "DSN=" + args.dsnName + ";"; } // add properties both for DSN or driver switch (args.auth_type) { case "win_auth": ret += "Authentication=Windows Authentication;"; break; case "sql_auth": ret += "UID=" + args.loginName + ";"; ret += "PWD=" + args.loginpassword + ";"; break; case "dsn_auth": default: break; } this.MyDriver = DBExecutor.UseDriver.ODBC; this.ConnectString = ret; return(ret); }
public string BuildConnectionString(string dbType, string dsnName, string auth_type, string server, string port, string database, string loginName, string loginpassword, string userDefinedDriverName, UseDriver useDriverType) { string ret = string.Empty; this.Server = server; if (dsnName != string.Empty) { // I wish to connect through named DSN, ODBC only useDriverType = UseDriver.ODBC; } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.ORACLE) { // built in native support for oracle and teradata if (dbType == "oracle") { if (string.IsNullOrEmpty(port)) { port = "1521"; } this.MyDriver = DBExecutor.UseDriver.ORACLE; this.ConnectString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={4})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={3})));User Id = {1}; Password = {2}; ", server, loginName, loginpassword, database, port); return this.ConnectString; } } if (useDriverType == UseDriver.DEFAULT || useDriverType == UseDriver.TERADATA) { // teradata - we have own driver if (dbType == "teradata") { this.MyDriver = DBExecutor.UseDriver.TERADATA; TdConnectionStringBuilder builder = new TdConnectionStringBuilder(); builder.SessionCharacterSet = "UTF8"; builder.DataSource = server; builder.UserId = loginName; builder.Password = loginpassword; this.ConnectString = builder.ToString(); return this.ConnectString; } } // the only solution is find an ODBC driver if (string.IsNullOrEmpty(dsnName)) { // I wish to go through driver string driverName = string.Empty; if (userDefinedDriverName == string.Empty) { List<string> drivers = ODBCUtils.GetSystemDriverList(); switch (dbType) { case "oracle": driverName = drivers.Where(x => x.IndexOf("Oracle") >= 0).FirstOrDefault(); break; case "mssql": default: driverName = drivers.Where(x => x.IndexOf("SQL Server") >= 0).FirstOrDefault(); break; } } else { driverName = userDefinedDriverName; } if (string.IsNullOrEmpty(driverName)) { throw new Exception("No ODBC driver found, please install and try again"); } else { ret += "Driver={" + driverName + "};"; } if (string.IsNullOrEmpty(port)) { ret += "Server=" + server + ";"; } else { ret += "Server=" + server + ":" + port + ";"; } if (!string.IsNullOrEmpty(database)) { ret += "Database=" + database + ";"; } } else { // I wish to go through named DSN ret = "DSN=" + dsnName + ";"; } // add properties both for DSN or driver switch (auth_type) { case "win_auth": ret += "Authentication=Windows Authentication;"; break; case "sql_auth": ret += "UID=" + loginName + ";"; ret += "PWD=" + loginpassword + ";"; break; case "dsn_auth": default: break; } this.MyDriver = DBExecutor.UseDriver.ODBC; this.ConnectString = ret; return ret; }
/// <summary>Initializes a new instance of the <see cref="TeradataDbService"/> class.</summary> /// <param name="connectionStringBuilder">The connection string builder.</param> /// <exception cref="ArgumentNullException"><paramref name="connectionStringBuilder"/> is <see langword="null"/>.</exception> public TeradataDbService(TdConnectionStringBuilder connectionStringBuilder) { _connectionStringBuilder = connectionStringBuilder ?? throw new ArgumentNullException(nameof(connectionStringBuilder)); }
static void Main(string[] args) { // Loosely based on https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples var user = Environment.GetEnvironmentVariable("USERNAME"); Console.Write($"User [{user}]: "); var userIn = Console.ReadLine(); user = String.IsNullOrWhiteSpace(userIn) ? user : userIn; Console.Write("Password: "******"server", UserId = user, Password = pass, // Optional AuthenticationMechanism = "LDAP", ConnectionPooling = true, DataEncryption = true, IntegratedSecurity = false, ResponseBufferSize = 7340000 }; Console.WriteLine("Connection string:"); Console.WriteLine(connStrbBuilder.ConnectionString); Console.WriteLine(); Console.WriteLine("Result:"); // "Data Encryption=True;Authentication Mechanism=LDAP;Response Buffer Size=7340000;User Id=xxx;Data Source=xxx;Password=xxxx;Connection Pooling=True;Integrated Security=False" // Provide the query string with a parameter placeholder. string queryString = "SELECT * FROM DBC.TablesV " + "WHERE TableKind = ? " + "SAMPLE 10;"; // Specify the parameter value. var param = new TdParameter("", "V"); // Create and open the connection in a using block. This // ensures that all resources will be closed and disposed // when the code exits. using (var connection = new TdConnection(connStrbBuilder.ConnectionString)) { // Create the Command and Parameter objects. var command = new TdCommand(queryString, connection); command.Parameters.Add(param); // Open the connection in a try/catch block. // Create and execute the DataReader, writing the result // set to the console window. try { connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader[2]); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.ReadLine(); } }